Seattle Software Developers


Pugetworks Blog Archive!

Viewing PostGIS Data

There are times when you need to sanity check your PostGIS geometric data. The tips below help you to accomplish just that and will work with Points and Polygons.

The Data

In your database, the data looks like: [sql]SELECT geom from my_table; 0103000020ED0800000100000075000000AEDD0240E88E334186F5FF7F63AD0A41D8C300C0 B28E33413F80E0FF7EAD0A415317FD9F768E3341C4AF0460C3AD0A417017FD9F768E33419F CB1140E1AD0A418DDB0240768E3341C23805405CAE0A4119E80300788E3341BD71E9DF15AF 0A410DAAFE3F7D8E33416EAC03A0E...[/sql]

This doesn't mean much to most of us, so this is how you make it meaningful.

The Functions

'Well-Known Text' representation

To get the data in an understandable form or 'Well-Known Text' (WKT) representation of the geometry just use the PostGIS function ST_AsText( obj ).

[sql]SELECT ST_AsText( geom ) from my_table; POLYGON((1281768.25004373 218540.437499922, 1281714.75001167 218543.87493992, 1281654.62495561 218552.421883939, 1281654.62495562 218556.156283942, 1281654.2500436 218571.53125996, 1281656.00005961 218594.734331978, 1281661.24997962 218620.203132007, 1281768.25004373 218540.437499922))[/sql]


But wait a minute! Those coordinates don't look to be Longitude/Latitude. In some cases, this could be true, but it is usually a good idea to verify the projection of the data. Most people are familiar with the 4326 projection, which Google Maps uses. Some older installations will use 900913 which Google Maps will also utilize. Most other projections are for specific geographic areas.

To get the projection or Spatial Reference System Identifier (SRID) of your data use the PostGIS function SRID( obj ).

[sql]SELECT SRID( geom ) from my_table; 2285[/sql]


It is very simple to 'transform' from one projection to another with the PostGIS function ST_Transform( obj, new_srid ).

[sql]SELECT ST_Transform( geom, 4326 ) from my_table;[/sql]

Ooops... don't forget to make it readable!

[sql]SELECT ST_AsText( ST_Transform( geom, 4326 ) from my_table; POLYGON((-122.286826974975 47.5898335515998, -122.287044005988 47.5898402034072, -122.287288271075 47.5898605168359, -122.287288556978 47.5898707528316, -122.287291253122 47.5899128762452, -122.287285938921 47.5899765665361, -122.287266617433 47.590046648457, -122.286820730691 47.5898457989397, -122.286826974975 47.5898335515998))[/sql]

Google Earth

So we can see the Longitude/Latitude information, but say we actually want to see the object on a map! With a little extra work, we can create a KML file that Google Earth will read and display our data.

First we need to get the Geometric Data by using the PostGIS function ST_AsKML( obj ) . Remember that Google Earth requires the 4326 projection! [sql]SELECT ST_AsKML( ST_Transform( geom, 4326 ) from my_table; <Polygon><outerBoundaryIs><LinearRing><coordinates> -122.286826974975,47.5898335515998,0 -122.287044005988,47.5898402034072,0 -122.287288271075,47.5898605168359,0 -122.287288556978,47.5898707528316,0 -122.287291253122,47.5899128762452,0 -122.287285938921,47.5899765665361,0 -122.287266617433,47.590046648457,0 -122.286820730691,47.5898457989397,0 -122.286826974975,47.5898335515998,0 </coordinates></LinearRing></outerBoundaryIs></Polygon>[/sql]

Next we need to compose a valid KML file. This is an xml based file that contains some style information and the geographic data we retrieved from the database. (A note about the color... they look like standard html RGB colors, but in fact... they are backwards... BGR with the transparency at the beginning, but that is for another blog...)

[xml]<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="http://earth.google.com/kml/2.1"> <Document> <Style id="astyle"> <LineStyle> <color>7f53892b</color> <width>2</width> </LineStyle> <PolyStyle> <color>7f79c63f</color> <colorMode>normal</colorMode> <fill>1</fill> <outline>1</outline> </PolyStyle> </Style>

<Placemark> <styleUrl>#astyle</styleUrl> {{ geographic data }} </Placemark> </Document> </kml>[/xml]

The end result of the KML file is as follows: test.kml

and when you open the kml file in Google Earth you should see:

Multiple Objects in KML

If you want to show many Points or Polygons, just duplicate the following section of the KML

[xml]<Placemark> <styleUrl>#astyle</styleUrl> {{ geographic data }} </Placemark>[/xml]