[postgis-users] Using PostGIS for latitude & longitude

[Nicolas Ribot]

Hi,

You have to cast the textual representation of the geometry into an actual
geometry, in the st_setSRID function, to resolve the ambiguity:

select state
from zipcode
where st_DISTANCE(column2,
ST_SetSRID('POINT(33.911404 -116.768347)'::geometry, 2163)) < 8000;

Nicolas


On 24 December 2012 13:32, nevillekb <nevillekb at gmail.com> wrote:

> Hi,
>
> So i created a table named zipcode with the following columns
>
> Id
> State
> Zip
> Latitude
> Longitude
> column1
> column2
>
> columns named column1 and column2 are the 2 geometry columns i have created
> to store the lat and long in 2 formats -SRID 4269 (Lat/Lon) and SRID 2163
> (US National Atlas – meters).
>
> Then i execute the following query to populate both the columns with the
> lat
> and lon data
>
> UPDATE zipcode SET column1 =
> ST_SetSRID(ST_MakePoint("longitude","latitude"),4269), column2 =
> ST_Transform(ST_SetSRID(ST_MakePoint("longitude","latitude"),4269), 2163);
>
> Everything has worked fine till now, but now when i try to execute the
> following query to show all records that are within 5 miles of a given
> point
> i get an error.
>
> Query:
>
> select state from zipcode where DISTANCE(column2,
> ST_SetSRID('POINT(33.911404 -116.768347)', 2163)) < 8000;
>
> Error Generated:
>
> WARNING  ] select state from zipcode where DISTANCE(column2,
> ST_SetSRID('POINT(33.911404 -116.768347)', 2163)) < 8000
>             ERROR:  function st_setsrid(unknown, integer) is not unique
>             LINE 1: select state from zipcode where DISTANCE(column2,
> ST_SetSRID...
>                                                                       ^
>             HINT:  Could not choose a best candidate function. You might
> need to add explicit type casts.
>
> Can someone help me out as to where am i going wrong in the above query??
>
> Thanks
>
>
>
> --
> View this message in context:
> http://postgis.17.n6.nabble.com/Using-PostGIS-for-latitude-longitude-tp5002097p5002125.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121224/63723725/attachment.html>