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