H2GIS

Fork me on GitHub

SHPWrite

Signatures

SHPWrite(VARCHAR path, VARCHAR tableName);
SHPWrite(VARCHAR path, VARCHAR tableName, VARCHAR fileEncoding);

Description

Writes the contents of table tableName to a shapefile located at path.

tablename can be either:

  • the name of an existing table,
  • the result of a query (SELECT instruction which has to be written between simple quote and parenthesis '( )').

The default value of fileEncoding is ISO-8859-1.

Shapefiles do not support arbitrary geometrical data.

They do not support:

  • POLYGONs (they are automatically converted to MULTIPOLYGONs when exported)
  • NULL Geometries
  • Multiple Geometry types in the same table

Examples

-- Create an example table containing POLYGONs and export it.
CREATE TABLE AREA(THE_GEOM GEOMETRY, ID INT PRIMARY KEY);
INSERT INTO AREA VALUES
    ('POLYGON((-10 109, 90 9, -10 9, -10 109))', 1),
    ('POLYGON((90 109, 190 9, 90 9, 90 109))', 2);
CALL SHPWrite('/home/user/area.shp', 'AREA');

-- Read it back, showing that the driver wrote POLYGONs as
-- MULTIPOLYGONs to be compatible with SHP.
CALL SHPRead('/home/user/area.shp', 'AREA2');
SELECT * FROM AREA2;
-- Answer:
-- |                     THE_GEOM                     | ID |
-- | ------------------------------------------------ | -- |
-- | MULTIPOLYGON(((-10 109,, 90 9, -10 9, -10 109))) |  1 |
-- | MULTIPOLYGON(((90 109, 190 109, 90 9, 90 109)))  |  2 |

Case where tablename is the result of a selection.

CALL SHPWRITE('/home/user/area.shp', 
              '(SELECT * FROM AREA WHERE ID<2 )');

-- Read it back
CALL SHPRead('/home/user/area.shp', 'AREA2');
SELECT * FROM AREA2;
-- Answer:
-- |                     THE_GEOM                     | ID |
-- | ------------------------------------------------ | -- |
-- | MULTIPOLYGON(((-10 109,, 90 9, -10 9, -10 109))) |  1 |

Export the .prj file

If you want to export your shapefile with it’s projection, stored in a .prj file, you must assume that the table contains a SRID constraint value greater than 0.

If not, the SRID must be enforced using the following commands:

UPDATE mytable SET the_geom = ST_SetSRID(the_geom, EPSG_CODE);
ALTER TABLE mytable ADD CHECK ST_SRID(the_geom) = EPSG_CODE;

Where:

  • mytable is the table name to update
  • the_geom is the geometric field name
  • EPSG_CODE is the EPSG id corresponding to your system (e.g 4326 for WGS84 or 2154 for the french Lambert 93).

Then export your shapefile as seen before.

See also