Creating a SHP from SQL

Question

Is it possible convert sql to shp file using pgsql2shp without shell? Through some SQL (postgis, postgresql) syntax?

Answer 1

It is not possible without calling the shell. However, two kuldges are available:

1) You could create a function in an unsafe language (like python) within postgresql that then executes pgsql2shp. This is an ugly hack, but has the advantage of producing a shapefile by SQL query.

2) You could dump the sql results into a temporary table with the geometry column saved as text via ST_AsText. You can then dump the table to csv via the sql COPY TO command, and then reconstruct a shapefile from the csv later using a client side script.

Neither of these approaches is entirely satisfactory (approach 2 in particular may lose some of the precision of your geometry), but they may be enough to achieve your goal.