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 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

# 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";


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)');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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