2

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.

1

For me very interesting question, because I like perl and with the new swig based Geo::OGR, Geo::GDAL bindings created with GDAL 2.0.2 (good stuff since version 1.11) and maintained by Ari Jolma it seems to be perfect to handle geo stuff in perl too.

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 infos and how to itereate over the features and create some SQL statements. I think perl API is very similar to the python API. I also avoid to handle the DBI stuff and generate a classical INSERT statement instead.

To get an idea how to use GDAL in perl you could use 80% of the python cookbook (language cross over) and the perl class reference for the rest.

I downlaod an example cities.geojson, thanks to Michael Mahemoff. All the settings in the perl file are static params ($file, $layer, $epsg, field keys) and matching the content of this file. I want only to get an idea how it works and feels.

Here the quick and dirty script called test-json.pl:

#!/usr/bin/perl -w
use strict;
use Geo::OGR;

# Set the driver name
my $dname  = 'GeoJSON';

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

# Set the layer name
my $lname = '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';

# Get the driver
my $driver    = Geo::OGR::GetDriverByName($dname)
   or die "Unkown driver $dname \n";

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

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

To iterate over the feature and print the INSERT statement, I use this loop:

# Open the layer for iteration again
my $layer = $dsource->GetLayerByName($lname)
    or die "Unkown layer $lname \n";

# Iterate over the features
while (my $data = $layer->GetNextFeature) {
    my $geom = $data->Geometry->AsText; # get the geom as WKT
    my $city = $data->{$key_city};      # get the city name
    $city = &escapeSqlStr($city);       # do some escaping without UTF8 ;-)
    # build the insert statement
    print "INSERT INTO $table ($key_city, $key_geom) VALUES\n".
        "($city, ST_GeomFromEWKT('SRID=$epsg;$geom');\n\n";
} # finished

The service routine to list the layes and attributes:

# sub routine to get the layers and attributes
# @param is a dataSource
sub listLayers() {
    my $dataSource = shift;
    # Iterate over the layers
    for my $lix (0..$dataSource->GetLayerCount-1) {
        my $layer = $dataSource->GetLayerByIndex($lix); # get the layer
        my $srs   = $layer->GetSpatialRef;   # get the spatial reference system
        my $gtype = $layer->GeometryType;    # get the geometry type
        my $numFt = $layer->GetFeatureCount; # get the number of features
        # Layer statistics
        print "layer: ",$lix+1,
            " name:", $layer->GetName,
            " features: ", $numFt,
            " type: ", $gtype,
            "\n  spatial-reference: ", $srs->Export('Proj4'),"\n";

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

And some Q&D escape and null handling stuff:

# sub routine for a minimal NULL and escape handling
# routine better done with DBI (no UTF8 URI handling here)
sub escapeSqlStr() {
    my $str = shift;
    return 'NULL' if ! $str;
    $str =~ s/'/\'/s;
    return "'".$str."'";
}

#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)');
...
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.