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 <[email protected]>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<[email protected]> 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 <[email protected]> > > 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<[email protected]> 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 > >> > [email protected] > >> > 704.414.0805 > >> > > >> > > >> > On Thu, Jul 9, 2009 at 1:38 PM, Bo Coughlin <[email protected]> wrote: > >> >> > >> >> Tried - got nothing returned...little scary. - bo > >> >> > >> >> Bo Coughlin > >> >> [email protected] > >> >> 704.414.0805 > >> >> > >> >> > >> >> On Thu, Jul 9, 2009 at 1:16 PM, Paul Ramsey < > [email protected]> > >> >> 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 > >> >>> [email protected] > >> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> >>> > >> >> > >> > > >> > > >> > _______________________________________________ > >> > postgis-users mailing list > >> > [email protected] > >> > http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > >> > > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
