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<br>
<br><br><div class="gmail_quote">On Thu, Jul 9, 2009 at 2:23 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;">
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>
<div><div></div><div class="h5"><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 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 USA...<br>
>><br>
>> select st_srid(the_geom), st_astext(the_geom) from market_locations 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 <<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>
</div></div></blockquote></div><br>