GML to database

How to start with GML, I don’t know. I have a GML file. It is 62 MB’s and it’s full of geospatial content with attributes and obviously geometries. I didn’t count how many geometries it included, but the XML file is in total was more 900K rows.

Just for curiosity reasons I had to open it with Notepad, or honestly with Textpad on Mac, before doing anything else. Nevertheless, I opened it and it was a complete set of Finnish topographic map with all the attributes. And all of that is open data!! Nice.

GML is mainly used for transformation purposes and therefore not meant to be used directly by GIS applications. Of course you can open it by using several applications, such as Snowflake GML Reader or Mapinfo or ESRI, but most likely you would first do some ETL (Extract Transform Load) to have the content available e.g in a database.

I took this GML file and made a XSL file to convert all the geometries and attributes into SQL to upload everything into a database. I first had a feeling it would have taken forever, but after few minutes (read hours) I had a simple  XSL file to upload content into PostGIS.

My example is simple and especially in this case not even containing all the attributes available in GML file, but gives an idea how you could do transformations from GML into SQL using XSLT.

Here is a snippet how to select buildings from Finnish MTK/GML using XSL and convert it into SQL insert clause. In this case geometry has been written into PostGIS geometry using ST_GeomFromGML function. A full example is available by request.

–Snippet original content —

<Rakennus gid=”778224154″ dimension=”3″>
<sijaintitarkkuus>5000</sijaintitarkkuus>
<aineistolahde>1</aineistolahde>
<alkupvm>2010-12-30</alkupvm>
<sijainti>
<Piste>
<gml:pos srsDimension=”3″>375532.120 6677599.357 0.000</gml:pos>
</Piste>
<Alue>
<gml:exterior>
<gml:LinearRing>
<gml:posList srsDimension=”3″>375572.435 6677622.029 24.106 375574.293 6677618.352 24.106 375491.797 6677576.688 24.106 375489.946 6677580.354 24.106 375572.435 6677622.029 24.106</gml:posList>
</gml:LinearRing>
</gml:exterior>
</Alue>
</sijainti>
<kohderyhma>75</kohderyhma>
<kohdeluokka>42261</kohdeluokka>
<korkeustarkkuus>201</korkeustarkkuus>
<kayttotarkoitus>6</kayttotarkoitus>
<kerrosluku>1</kerrosluku>
<pohjanKorkeus>24106</pohjanKorkeus>
</Rakennus>

–Snippet original content ends—

— Snippet xsl —

<!– RAKENNUS –>
<xsl:for-each select=”/mtj:Maastotiedot/mtj:rakennukset/mtj:Rakennus”>
insert into rakennus(gid, kohderyhma, kohdeluokka, sijainti) values (<xsl:value-of select=”./@gid”/>, <xsl:value-of select=”./mtj:kohderyhma”/>,<xsl:value-of select=”./mtj:kohdeluokka”/>,ST_GeomFromGML(‘<gml:Polygon><xsl:copy-of select=”./mtj:sijainti/mtj:Alue/*”/></gml:Polygon>’ ,3067));
</xsl:for-each>

— Snippet xsl ends —

— Snippet result —

insert into rakennus(gid, kohderyhma, kohdeluokka, sijainti) values (417661315, 75,42211,ST_GeomFromGML(‘<gml:Polygon xmlns:mtj=”http://xml.nls.fi/XML/Namespace/Maastotietojarjestelma/SiirtotiedostonMalli/2011-02″><gml:exterior xmlns=”http://xml.nls.fi/XML/Namespace/Maastotietojarjestelma/SiirtotiedostonMalli/2011-02″ xmlns:gml=”http://www.opengis.net/gml” xmlns:xlink=”http://www.w3.org/1999/xlink” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”><gml:LinearRing><gml:posList srsDimension=”3″>374433.004 6677044.086 26.547 374483.539 6677071.489 26.547 374488.929 6677061.547 26.547 374438.393 6677034.146 26.547 374433.004 6677044.086 26.547</gml:posList></gml:LinearRing></gml:exterior></gml:Polygon>’ ,3067));

— Snippet result ends—