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<[email protected]> 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<[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
