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.<div>
<br><br><div class="gmail_quote">On Thu, Jul 9, 2009 at 2:45 PM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
BTW, you never ran these queries:<br>
<br>
select as_astext(st_extent(the_geom)) from market_locations;<br>
select as_astext(st_extent(the_geom)) from customer_locations;<br>
<div><div></div><div class="h5"><br>
<br>
On Thu, Jul 9, 2009 at 11:44 AM, Paul Ramsey<<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<br>
> If you only have data in the east, how does a correct SD record, with<br>
> a correct SD zip code, correct SD city name, and correct SD<br>
> coordinates show up? I think you need to take a coffee break.<br>
><br>
> P<br>
><br>
> On Thu, Jul 9, 2009 at 11:27 AM, Bo Coughlin<<a href="mailto:bo@rekindl.com">bo@rekindl.com</a>> wrote:<br>
>> I have a foreign key 'buiding_id" to match on. so I know its the same<br>
>> record. In addition all locations are in VA/WV/PA/DE/MD - - I must have<br>
>> transformed somehow during my process for I am an<br>
>> 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<br>
>><br>
>><br>
>> On Thu, Jul 9, 2009 at 2:23 PM, Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>><br>
>> wrote:<br>
>>><br>
>>> Are you referring to the first record? There is no ordering guarantee<br>
>>> in database tables. The first record in your file could be the<br>
>>> 12,000th in the database. The coordinates of that south dakota record<br>
>>> do look to be in south dakota. Seems a good sign to me.<br>
>>><br>
>>> On Thu, Jul 9, 2009 at 11:20 AM, Bo Coughlin<<a href="mailto:bo@rekindl.com">bo@rekindl.com</a>> wrote:<br>
>>> > right. but the location (in raw original) is:<br>
>>> > 1442 E FORT AVE BALTIMORE MD 21230 39.269318 -76.594162<br>
>>> > where it is now:<br>
>>> > 308th Aveu Gettysburg, SD 57442POINT(-100.000971435723<br>
>>> > 45.0003533555503)<br>
>>> ><br>
>>> > that's a bad thing...<br>
>>> ><br>
>>> ><br>
>>> > On Thu, Jul 9, 2009 at 2:06 PM, Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>><br>
>>> > wrote:<br>
>>> >><br>
>>> >> The coordinates you pasted in are valid points in the continental<br>
>>> >> USA...<br>
>>> >><br>
>>> >> select st_srid(the_geom), st_astext(the_geom) from market_locations<br>
>>> >> limit<br>
>>> >> 1;<br>
>>> >> 4326 POINT(-77.1042756692815 38.995397678429)<br>
>>> >><br>
>>> >> select st_srid(the_geom), st_astext(the_geom) from customer_locations<br>
>>> >> limit 1;<br>
>>> >> 4326 POINT(-100.000971435723 45.0003533555503)<br>
>>> >><br>
>>> >> P<br>
>>> >><br>
>>> >> On Thu, Jul 9, 2009 at 10:46 AM, Bo Coughlin<<a href="mailto:bo@rekindl.com">bo@rekindl.com</a>> wrote:<br>
>>> >> > Hmmm....I just looked again at the points - they no longer match the<br>
>>> >> > original lat/lon coordinates, could I have somehow altered these via<br>
>>> >> > reprojecting them?<br>
>>> >> > Bo Coughlin<br>
>>> >> > <a href="mailto:bo@rekindl.com">bo@rekindl.com</a><br>
>>> >> > 704.414.0805<br>
>>> >> ><br>
>>> >> ><br>
>>> >> > On Thu, Jul 9, 2009 at 1:38 PM, Bo Coughlin <<a href="mailto:bo@rekindl.com">bo@rekindl.com</a>> wrote:<br>
>>> >> >><br>
>>> >> >> Tried - got nothing returned...little scary. - bo<br>
>>> >> >><br>
>>> >> >> Bo Coughlin<br>
>>> >> >> <a href="mailto:bo@rekindl.com">bo@rekindl.com</a><br>
>>> >> >> 704.414.0805<br>
>>> >> >><br>
>>> >> >><br>
>>> >> >> On Thu, Jul 9, 2009 at 1:16 PM, Paul Ramsey<br>
>>> >> >> <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>><br>
>>> >> >> wrote:<br>
>>> >> >>><br>
>>> >> >>> Try this:<br>
>>> >> >>><br>
>>> >> >>> --<br>
>>> >> >>> -- ST_DWithin_Sphere(lonlat-point, lonlat-point, radius-in-meters)<br>
>>> >> >>> returns boolean<br>
>>> >> >>> --<br>
>>> >> >>> -- Meters/Degree @ 60N: select 1/distance_sphere('POINT(0<br>
>>> >> >>> 60)','POINT(1 60)') = 1.79866403673916e-05<br>
>>> >> >>> --<br>
>>> >> >>> CREATE OR REPLACE FUNCTION ST_DWithin_Sphere(geometry, geometry,<br>
>>> >> >>> float8)<br>
>>> >> >>> RETURNS boolean<br>
>>> >> >>> AS 'SELECT $1 && ST_Expand($2,$3 * 1.79866403673916e-05) AND<br>
>>> >> >>> $2 && ST_Expand($1,$3 * 1.79866403673916e-05) AND<br>
>>> >> >>> ST_Distance_Sphere($1, $2) < $3'<br>
>>> >> >>> LANGUAGE 'SQL' IMMUTABLE;<br>
>>> >> >>><br>
>>> >> >>><br>
>>> >> >>> SELECT <a href="http://m.id" target="_blank">m.id</a> AS mid,<br>
>>> >> >>> m.building_i AS mb_id,<br>
>>> >> >>> <a href="http://c.id" target="_blank">c.id</a> AS cid, c.building_i AS cb_id,<br>
>>> >> >>> m.streetaddr AS m_address,<br>
>>> >> >>> m.city AS m_city,<br>
>>> >> >>> m.state AS m_state,<br>
>>> >> >>> m.zip AS m_zip,<br>
>>> >> >>> m.zip4 AS m_zip4,<br>
>>> >> >>> round(CAST(<br>
>>> >> >>> ST_Distance_Sphere(m.the_geom, c.the_geom)<br>
>>> >> >>> AS numeric), 2) AS dist_meters<br>
>>> >> >>> FROM market_locations m, customer_locations c<br>
>>> >> >>> WHERE st_dwithin_sphere(m.the_geom, c.the_geom, 2414.016)<br>
>>> >> >>> _______________________________________________<br>
>>> >> >>> postgis-users mailing list<br>
>>> >> >>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>>> >> >>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>>> >> >>><br>
>>> >> >><br>
>>> >> ><br>
>>> >> ><br>
>>> >> > _______________________________________________<br>
>>> >> > postgis-users mailing list<br>
>>> >> > <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>>> >> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>>> >> ><br>
>>> >> ><br>
>>> >> _______________________________________________<br>
>>> >> postgis-users mailing list<br>
>>> >> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>>> >> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>>> >><br>
>>> ><br>
>>> ><br>
>>> > _______________________________________________<br>
>>> > postgis-users mailing list<br>
>>> > <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>>> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>>> ><br>
>>> ><br>
>>> _______________________________________________<br>
>>> postgis-users mailing list<br>
>>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>>><br>
>><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>><br>
>><br>
><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
</div></div></blockquote></div><br></div>