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

[Bo Coughlin]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090709/b1fe1d2a/attachment.html>