Working with Geography and Geometry Data

If your PostgreSQL instance includes the PostGIS extension, you can use PROC SQL to Read and work with geography or geometry values. SAS checks for the version of PostGIS that is installed to ensure that the extension is present. If the PostgreSQL server that you specify does not have PostGIS installed, SAS writes an error to the SAS log.

Geography and geometry data is represented as VARCHAR values in SAS. These values, such as 010100000000000000000000000000000000000000, are not very useful. However, if you use available PostGIS functions, such as ST_AsText, then data points are rendered in a more understandable form. Other functions that help you to render geography and geometry data in a readable form are ST_AsEWKT, ST_AsGML, ST_AsGeoJSON, and ST_AsSVG.

For example, the following query to a table called Geometries with libref Mydb renders data similar to the output shown below:

proc sql;
select name, ST_AsText(geom) as geom from mydb.geometries;
quit;

Sample Output from PostGIS Query

name	geom
Point	POINT(0 0)
Linestring	LINESTRING(0 0,1 1,2 1,2 2)
Polygon	POLYGON((0 0,1 0,1 1,0 1,0 0))
PolygonWithHole	POLYGON((0 0,10 0,10 10,0 10,0 0),(1 1,1 2,2 2,2 1,1 1))
Collection	GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0,1 0,1 1,0 1,0 0)))

Here is the list of PostGIS functions that you can call with PROC SQL:

For descriptions and explanations about usage of these functions, see your PostGIS documentation.