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

Thu Jul 9 11:54:00 PDT 2009

I simply input the lat/lon into teleatlas and the response is what I pasted
into last email.  Didn't have anything other than the lat/lon.  The queries
keep throwing cast errors, i have to take a look.  as for coffee break, no
doubt...but I have to get these measurements to client by COB today.

On Thu, Jul 9, 2009 at 2:45 PM, Paul Ramsey <pramsey at cleverelephant.ca>wrote:

> 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
> >>
> >>
> >
> _______________________________________________
> 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/7139bbe4/attachment.html>