Importing Data from Google Earth to PostgreSQL

This article discusses how to utilize PostgreSQL to import crucial data and geometry segments from Google Earth and convert KML files into ESRI format files.

By digoal

Background

Keyhole Markup Language (KML) is a file format used to display geographic data, which uses a structure (tag-based) containing nested elements and attributes and conforms to the Extensible Markup Language (XML) standard.

Because KML has been added to the Open Geospatial Consortium (OGC) standard, PostGIS, as a member of OGC, also supports the KML format. You can use ST_GeomFromKML to import geometry segments to PostgreSQL in the KML format.

geometry ST_GeomFromKML(text geomkml);

Example:

SELECT ST_GeomFromKML('      
<LineString>      
<coordinates>-71.1663,42.2614      
-71.1667,42.2616</coordinates>      
</LineString>');

You can also use ogr2ogr to convert KML (including KMZ, with a LibKML dependency) files into Environmental Systems Research Institute (ESRI) format files. Then you can use the shp2pgsql data loader to convert the ESRI files into SQL suitable for insertion into PostgreSQL.

Building a 3D GIS Application Using GeoServer and Google Earth

To add height data to layers, you need to process the data separately based on the entities' geometry type. For planes, such as administrative boundaries and buildings, you must create a height template file.

For lines (points), such as aerial cables, the elevation data is directly read from the drawings.

GeoServer 1.7.1 and later versions can generate KML (KMZ) files for 3D planes. GeoServer 1.7.6 and later versions can generate KML (KMZ) files for 3D lines (points).

Publishing 3D Plane Layers

The process of using Geoserver to publish a 3D plane is straightforward. In the test, we use the Geoserver sample layer topp:states.

1) Use Notepad to create a new text file (height template file) and name it as height.ftl.

2) Add a script in the format of ${ATTRIBUTE.value} and name the attribute field as ATTRIBUTE.

For example, the states layer has the PERSONS attribute field to record the population of each state. Enter ${PERSONS.value? number/100} to correlate the height with the population.

3) Copy height.ftl to /data/featureTypes/states/ of GeoServer.

4) Enter the GeoServer map preview page, find topp:states, select KML, and open Google Earth to view the result.

Display effect 1:

To read the official document, visit http://geoserver.org/display/GEOSDOC/04-Height+Templates

As a practice, refer to the official document to publish buildings in Manhattan, New York and view the display effect of 3D City.

Display effect 2:

Publishing 3D Line Layers

3D lines do not use the height.ftl file. Because a line has more than two nodes, each node may have different height values, and the elevation is directly stored in z coordinates of the graphic data (geometry).

You can store 3D coordinates in databases such as Oracle Spatial and PostgreSQL (PostGIS).

GeoServer can directly publish 3D lines. As a test, you can use Oracle Spatial (10g) and PostgreSQL (8.3) as the data source.

1) Store 3D line data with z coordinates in Oracle Spatial (10g) or PostgreSQL (8.3). In the test, you can directly insert data using SQL statements.

2) Publish the layer in GeoServer.

3) On the GeoServer map preview page, find the layer you just published. Then select KML and open Google Earth to view the result.

Note that KML's (KMZ) actual display effect is a 3D plane in Google Earth by default.

To implement a 3D line, append the optional parameter format_options=extrude:0 to the end of the KML link.

For example, change http://192.98.151.200:8080/geoserver/wms/kml?layers=topp:TESTL to http://192.98.151.200:8080/geoserver/wms/kml?layers=topp:TESTL&format_options=extrude:0

Display effect 3

Google KML Version 2.2 has been approved by OGC as an international standard for data storage. The latest versions of ESRI, INTERGRAPH, and MAPINFO can generate KML format files.

Google-defined KML can include 3D elevation data as well as 4D time data.

This article only covers the publishing and display of 3D data. For more information about the related technical documentation, see Google KML and Geoserver KML pages.

Google KML page: http://code.google.com/apis/kml/documentation /

GeoServer KML pages:

Software used in this article: GeoServer 1.7.7, GeoServer 2.0, Google Earth 5.1, Oracle 10.2.0.3, and PostgreSQL.

Community

Importing Data from Google Earth to PostgreSQL

Background

Building a 3D GIS Application Using GeoServer and Google Earth

Publishing 3D Plane Layers

Publishing 3D Line Layers

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

Database Overview

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL

Database for FinTech Solution