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<[email protected]> 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 <[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 > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
