Writing GeoJSON data into PostGIS in perl?

Question

I am working with GeoJSON files and need to store them in PostgreSQL using perl. I can do this in 2 steps:

open (JSON,">$path/tmp/current.json");
print JSON $fullJSON;
close JSON;

followed by:

`/usr/bin/ogr2ogr -f "PostgreSQL" PG:"dbname=database user=datauser" "$path/tmp/current.json" -nln datatable -append`;
 unlink ("$path/tmp/current.json");

But I was hoping for a pure perl solution, without using the ogr2ogr executable. I was reading about Geo::GDAL, but not sure if this is the right tool.

I was experimenting a bit with some code I found:

my $sth = $dbh->prepare("INSERT INTO datatable (geom) VALUES (GeomFromEWKT('SRID=?;POINT(? ? ?)'));")

But couldn't quite get the syntax right. The whole JSON string is: $fullJSON

If anyone has any info or code to perform this task, I'd love to see it. It seems pretty straight forward.

Answer 1

For me very interesting question, because I like Perl and gave the new Swig based Geo::GDAL bindings to the GDAL 2.0.2 library a try. The library is maintained by Ari Jolma.

In short: You should use the professional ogr2ogr tool and the following script is more a how to use GDAL with Perl, how to read the layer info's and how to iterate over the features and create some SQL statements. I think Perl API is very similar to the python API. To get an idea how to use GDAL in Perl you could use 80% of the python cookbook (language bindings cross over) and the Perl class reference for the rest.

I download an example cities.geojson, thanks to Michael Mahemoff. The quick and dirty coded script called test-json.pl reads GeoJSON in a GDAL like way and print SQL statments.

You can download the code and corresponding data from https://github.com/bigopensky/gis-se-snippet/tree/master/geo-json-perl.

Demo Script

use strict;
use warnings;

use Geo::GDAL;
use Data::Dumper;

# Data download of the geoJSON file from:
# https://raw.githubusercontent.com/mahemoff/geodata/master/cities.geojson
# Set the driver name
my $drvName  = 'GeoJSON';

# Set the json file
my $file   = 'cities.geojson';

# Set the layer name
my $lyrName = 'OGRGeoJSON';

# Set the city column name
my $key_city = 'city';

# Set the geometry column name
my $key_geom = 'geom';

# Set the table name
my $table = 'table'; 

# Set EPSG
my $epsg = '4326';

# print join("\n",Geo::OGR::GetDriverNames),"\n";

# Get the OGR driver
my $driver   = Geo::OGR::Driver($drvName)
   or die "Unknown driver $drvName \n";

# Open the file 
my $dataSource   = $driver->Open($file)
   or die "Unknown file $file \n";

# List the layers to investigate the stuff
# to get all the settings above key_..., epsg
&listLayers($dataSource);

# Open the layer
my $layer = $dataSource->GetLayerByName($lyrName)
    or die "Unknown layer $lyrName \n";

# Iterate over the features
while (my $data = $layer->GetNextFeature) {
    my $geom = $data->Geometry->AsText;
    my $city = $data->{$key_city};
    $city    = &escapeSqlStr($city);
    print "INSERT INTO $table ($key_city, $key_geom) VALUES\n".
          "($city, ST_GeomFromEWKT('SRID=$epsg;$geom');\n\n";
}

# =========================================
# Service routines 
# =========================================

# -----------------------------------------
# A minimal NULL and escaping routine
# Q&D ..better done with DBI
sub escapeSqlStr() {
    my $str = shift;
    return 'NULL' if ! $str;
    $str =~ s/'/\'/s;
    return "'".$str."'";
}

# -----------------------------------------
# List features within the layers
sub listLayers() {
    my $dataSource = shift;
    # Iterate over the layers
    for my $lix (0..$dataSource->GetLayerCount-1) {
        my $layer = $dataSource->GetLayerByIndex($lix);
        my $srs   = $layer->GetSpatialRef;
        $srs = $srs ? $srs->Export('Proj4') : 'UNKNOWN';
        my $gtype = $layer->GeometryType;
        my $numFt = $layer->GetFeatureCount;

        
        # Layer statistics
        print "layer: ",$lix+1,
            " name:", $layer->GetName,
            " features: ", $numFt,
            " type: ", $gtype,
            "\n  spatial-reference: $srs\n";

        # List attributes
        my $layerDefn = $layer->GetLayerDefn();
        for my $fix (0..$layerDefn->GetFieldCount-1) {
            my $fieldDefn = $layerDefn->GetFieldDefn($fix);
            print "  attribute: ", $fix+1,
                " name: ", $fieldDefn->Name,
                " type: ",$fieldDefn->Type,"\n";
        }
        print "\n";
    }
}

# EOF

The script gives me this output:

$ ./test-json.pl | head

layer: 1 name:OGRGeoJSON features: 661 type: Point
  spatial-reference: +proj=longlat +datum=WGS84 +no_defs 
  attribute: 1 name: wikipedia type: String
  attribute: 2 name: city type: String

INSERT INTO table (city, geom) VALUES
('Adak', ST_GeomFromEWKT('SRID=4326;POINT (-176.633 51.883)');
...