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.
Best Answer
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/