[postgis-users] st_dist, st_sphere, st_spheroid - overhead/efficiency question

[Paul Ramsey]

BTW, you never ran these queries:

select as_astext(st_extent(the_geom)) from market_locations;
select as_astext(st_extent(the_geom)) from customer_locations;


On Thu, Jul 9, 2009 at 11:44 AM, Paul Ramsey<pramsey at cleverelephant.ca> wrote:
> If you only have data in the east, how does a correct SD record, with
> a correct SD zip code, correct SD city name, and correct SD
> coordinates show up? I think you need to take a coffee break.
>
> P
>
> On Thu, Jul 9, 2009 at 11:27 AM, Bo Coughlin<bo at rekindl.com> wrote:
>> I have a foreign key 'buiding_id" to match on.  so I know its the same
>> record. In addition all locations are in VA/WV/PA/DE/MD - - I must have
>> transformed somehow during my process for I am an
>> idiot. you think it would be better to get projections GDAL/OGR then create geom shape? or am i overdoing it? then I can run the distance thing ...again. - bo
>>
>>
>> On Thu, Jul 9, 2009 at 2:23 PM, Paul Ramsey <pramsey at cleverelephant.ca>
>> wrote:
>>>
>>> Are you referring to the first record? There is no ordering guarantee
>>> in database tables. The first record in your file could be the
>>> 12,000th in the database. The coordinates of that south dakota record
>>> do look to be in south dakota. Seems a good sign to me.
>>>
>>> On Thu, Jul 9, 2009 at 11:20 AM, Bo Coughlin<bo at rekindl.com> wrote:
>>> > right. but the location (in raw original) is:
>>> > 1442 E FORT AVE  BALTIMORE MD 21230  39.269318 -76.594162
>>> > where it is now:
>>> > 308th Aveu  Gettysburg, SD 57442POINT(-100.000971435723
>>> > 45.0003533555503)
>>> >
>>> > that's a bad thing...
>>> >
>>> >
>>> > On Thu, Jul 9, 2009 at 2:06 PM, Paul Ramsey <pramsey at cleverelephant.ca>
>>> > wrote:
>>> >>
>>> >> The coordinates you pasted in are valid points in the continental
>>> >> USA...
>>> >>
>>> >> select st_srid(the_geom), st_astext(the_geom) from market_locations
>>> >> limit
>>> >> 1;
>>> >> 4326    POINT(-77.1042756692815 38.995397678429)
>>> >>
>>> >> select st_srid(the_geom), st_astext(the_geom) from customer_locations
>>> >> limit 1;
>>> >> 4326    POINT(-100.000971435723 45.0003533555503)
>>> >>
>>> >> P
>>> >>
>>> >> On Thu, Jul 9, 2009 at 10:46 AM, Bo Coughlin<bo at rekindl.com> wrote:
>>> >> > Hmmm....I just looked again at the points - they no longer match the
>>> >> > original lat/lon coordinates, could I have somehow altered these via
>>> >> > reprojecting them?
>>> >> > Bo Coughlin
>>> >> > bo at rekindl.com
>>> >> > 704.414.0805
>>> >> >
>>> >> >
>>> >> > On Thu, Jul 9, 2009 at 1:38 PM, Bo Coughlin <bo at rekindl.com> wrote:
>>> >> >>
>>> >> >> Tried - got nothing returned...little scary. - bo
>>> >> >>
>>> >> >> Bo Coughlin
>>> >> >> bo at rekindl.com
>>> >> >> 704.414.0805
>>> >> >>
>>> >> >>
>>> >> >> On Thu, Jul 9, 2009 at 1:16 PM, Paul Ramsey
>>> >> >> <pramsey at cleverelephant.ca>
>>> >> >> wrote:
>>> >> >>>
>>> >> >>> Try this:
>>> >> >>>
>>> >> >>> --
>>> >> >>> -- ST_DWithin_Sphere(lonlat-point, lonlat-point, radius-in-meters)
>>> >> >>> returns boolean
>>> >> >>> --
>>> >> >>> -- Meters/Degree @ 60N: select 1/distance_sphere('POINT(0
>>> >> >>> 60)','POINT(1 60)') = 1.79866403673916e-05
>>> >> >>> --
>>> >> >>> CREATE OR REPLACE FUNCTION ST_DWithin_Sphere(geometry, geometry,
>>> >> >>> float8)
>>> >> >>>        RETURNS boolean
>>> >> >>>        AS 'SELECT $1 && ST_Expand($2,$3 * 1.79866403673916e-05) AND
>>> >> >>> $2 && ST_Expand($1,$3 * 1.79866403673916e-05) AND
>>> >> >>> ST_Distance_Sphere($1, $2) < $3'
>>> >> >>>        LANGUAGE 'SQL' IMMUTABLE;
>>> >> >>>
>>> >> >>>
>>> >> >>> SELECT m.id AS mid,
>>> >> >>> m.building_i AS mb_id,
>>> >> >>> c.id AS cid, c.building_i AS cb_id,
>>> >> >>> m.streetaddr AS m_address,
>>> >> >>> m.city AS m_city,
>>> >> >>> m.state AS m_state,
>>> >> >>> m.zip AS m_zip,
>>> >> >>> m.zip4 AS m_zip4,
>>> >> >>> round(CAST(
>>> >> >>> ST_Distance_Sphere(m.the_geom, c.the_geom)
>>> >> >>> AS numeric), 2) AS dist_meters
>>> >> >>> FROM market_locations m, customer_locations c
>>> >> >>> WHERE st_dwithin_sphere(m.the_geom, c.the_geom, 2414.016)
>>> >> >>> _______________________________________________
>>> >> >>> postgis-users mailing list
>>> >> >>> postgis-users at postgis.refractions.net
>>> >> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> >> >>>
>>> >> >>
>>> >> >
>>> >> >
>>> >> > _______________________________________________
>>> >> > postgis-users mailing list
>>> >> > postgis-users at postgis.refractions.net
>>> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> >> >
>>> >> >
>>> >> _______________________________________________
>>> >> postgis-users mailing list
>>> >> postgis-users at postgis.refractions.net
>>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> >>
>>> >
>>> >
>>> > _______________________________________________
>>> > postgis-users mailing list
>>> > postgis-users at postgis.refractions.net
>>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> >
>>> >
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>