3

I am using a PostGIS query to find the intersection of two streets, and then transform the results into lat/lon. The street database's current SRID is 6856, a projection I added to PostGIS myself; and the target SRID is 4326. For reasons unknown, the transformation is not working. Here is my original query, which produces a point in feet:

SELECT distinct(AsText(intersection(b.geom, a.geom))) AS the_intersection FROM (SELECT geom FROM public.streets WHERE streetname='LOVEJOY' AND prefix='NW' AND ftype='ST') a, (SELECT geom FROM public.streets WHERE streetname='23RD' AND prefix='NW' AND ftype='AVE') b WHERE a.geom && b.geom AND intersects (b.geom, a.geom);

And here is my modified query, in which I attempt to transform the feet to lat/lon:

SELECT distinct(AsText(ST_Transform(intersection(b.geom, a.geom),4326))) AS the_intersection FROM (SELECT geom FROM public.streets WHERE streetname='LOVEJOY' AND prefix='NW' AND ftype='ST') a, (SELECT geom FROM public.streets WHERE streetname='23RD' AND prefix='NW' AND ftype='AVE') b WHERE a.geom && b.geom AND intersects (b.geom, a.geom);

This is my error message:

ERROR:  AddToPROJ4SRSCache: could not parse proj4 string ''

I have verified that both 6856 and 4326 are available in my spatial_ref_sys. Any insights would be appreciated.

Improve this question
2
  • 2
    I don't see any EPSG:6856 SRS either in my PostGIS or on spatialreference.org. Maybe the proj4 string is indeed not formatted correctly?
    – Micha
    Commented Oct 28, 2012 at 19:56
  • Dang, it's not. I used the PostGIS insert code from spatialreference.org, which for some reason has srtext but not proj4text: spatialreference.org/ref/sr-org/6856/postgis
    – L_Holcombe
    Commented Oct 28, 2012 at 21:59

1 Answer 1

Reset to default
5

Research indicates that this problem is caused by having an incorrect proj4 string in the proj4text field of the spatial_ref_sys table. I didn't think this was possible, because I got the PostGIS insert statement for this projection (6856) directly from spatialreference.org. But as you can see from the insert text, spatialreference.org provides srtext, but not proj4text. Updating the table with the correct proj4text should solve the problem.

And in the future, I shall be more watchful of the information I get from spatialreference.org.

http://spatialreference.org/ref/sr-org/6856/postgis/

Improve this answer
3
  • The Fix: UPDATE spatial_ref_sys SET proj4text='+proj=lcc +lat_1=44.33333333333334 +lat_2=46.0 +lat_0=43.66666666666666 +lon_0=-120.5 +x_0=8202099.737532808 +y_0=0 +ellps=GRS80 +units=ft +to_meter=0.3048 +no_defs' WHERE srid=6856; The main difference between 6856 and EPSG:2838 is that 6856 uses international feet instead of meters. Also, for some reason, the reference parallels are switched.
    – L_Holcombe
    Commented Oct 28, 2012 at 23:20
  • Nightmare! The false easting needs to be in meters, even if the projection units are feet. +x_0=2500000
    – L_Holcombe
    Commented Oct 29, 2012 at 0:28
  • To anyone having this problem, the PostGIS string from epsg.io has worked to solve this issue for me with a different spatial reference system (900913). I deleted the existing definition of 900913 from the spatial_ref_sys table, then used the insert string from epsg.io to insert the correct definition.
    – jbalk
    Commented Nov 18, 2021 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.