PostGIS FAQ

Frequently Asked Questions about PostGIS extension.

1. PostgreSQL Can't install under Windows Vista
2. How to create geometry from hex-encoded Well-Known-Binary string using SQL?
3. How to write geometry Well-Known-Binary to hex-encoded string?
4. When were the ST_* functions first added to match the SQL-MM-centric …

PostgreSQL Can't install under Windows Vista

Refer to these instructions Issues with installing under Windows Vista

How to create geometry from hex-encoded Well-Known-Binary string using SQL?

This is two-steps operation which can be executed in single ​SQL statement. The steps include:

• Convert ​Well-Known-Binary data in hexadecimal string form to raw binary using PostgreSQL function ​decode.
• Construct geometry object from WKB in raw binary form.

Example:

=# SELECT ST_AsText(ST_GeomFromWKB(decode('0101000000e5d022dbf93e2e40dbf97e6abc743540', 'hex'), -1));
      st_astext       
----------------------
 POINT(15.123 21.456)

How to write geometry Well-Known-Binary to hex-encoded string?

First, query for geometry object in ​Well-Known-Binary format using ​ST_AsBinary function. Then, ​encode raw WKB to hex string.

Examples:

• selecting geometry from table:

# SELECT encode(ST_AsBinary(the_geom), 'hex');
                   encode                   
--------------------------------------------
 0101000000e5d022dbf93e2e40dbf97e6abc743540

• constructing geometry in-place from given Well-Known-Text:

# SELECT encode(ST_AsBinary(ST_GeomFromText('POINT(15.123 21.456)', -1)), 'hex');
                   encode                   
--------------------------------------------
 0101000000e5d022dbf93e2e40dbf97e6abc743540

When were the ST_* functions first added to match the SQL-MM-centric convention?

As of PostGIS 1.3 the renaming has been in effect, as the 1.3 tag is the first appearance of ST_ prefixed functions in source:tags/1.3.0/lwgeom/lwpostgis.sql.in​

Therefore, non-prefixed functions (with ST_* equivalents) are deprecated, and since the 1.3 release may not be maintained in the same way as the official ST_* functions, so don't expect them to work the same.

For example only ST_AsBinary has been upgraded to work with the new geography type because AsBinary should not be being used anymore.

Application developers should consider upgrading their code to use ST_* functions as soon as possible, therefore requiring at least PostGIS ≥1.3.

As Background: The official PostGIS documentation ​| states that most functions have been renamed and "The non ST_ functions not listed in this documentation are deprecated and will be removed in a future release so STOP USING THEM."