Home > Uncategorized > Geoserver: Using postgresql as a store

Geoserver: Using postgresql as a store

We have seen that Shapefiles can be used as data stores for Geoserver. A multitude of other stores exist. An important on is the postgresql store. In this post we will create a database and a table, populate it with some geographical objects and load that data into geoserver.

Assuming that postgres is installed, along with the postgis extension, this powershell script will create a database;

Add-Type -Path "$psscriptroot\Npgsql.dll"

$builder = New-Object Npgsql.NpgsqlConnectionStringBuilder
$builder.Host = "localhost"
$builder.Username = "postgres"
$builder.Password = "some-password"
$connection = New-Object Npgsql.NpgsqlConnection $builder
$connection.Open()

$createdbcommand = $connection.CreateCommand()
$createdbcommand.CommandText = "CREATE DATABASE geoserverdb OWNER geoserver ENCODING 'UTF8' TABLESPACE pg_default"
$createdbcommand.ExecuteNonQuery() | Out-Null

$connection.Close()

This script creates a table:

Add-Type -Path "$psscriptroot\Npgsql.dll"

$builder = New-Object Npgsql.NpgsqlConnectionStringBuilder
$builder.Host = "localhost"
$builder.Username = "postgres"
$builder.Password = "some-password"
$builder.Database = "geoserverdb"
$connection = New-Object Npgsql.NpgsqlConnection $builder
$connection.Open()

$createTableCommand = $connection.CreateCommand()
$createTableCommand.CommandText = @"
CREATE EXTENSION postgis;

CREATE TABLE testtable
(
id bigserial PRIMARY KEY,
name character varying(64),
geoloc geometry
)
WITH (
OIDS=FALSE
);
ALTER TABLE testtable
OWNER TO geoserver;

CREATE INDEX testtable_geometry_index ON testtable USING GIST (geoloc);
"@

$createTableCommand.ExecuteNonQuery() | Out-Null

$connection.Close()
 

With this in place we can insert some random points using this script:


Add-Type -Path "$psscriptroot\Npgsql.dll"

$builder = New-Object Npgsql.NpgsqlConnectionStringBuilder
$builder.Host = "localhost"
$builder.Username = "geoserver"
$builder.Password = "some-password"
$builder.Database = "geoserverdb"
$connection = New-Object Npgsql.NpgsqlConnection $builder
$connection.Open()

for($i=0; $i -lt 1000; $i++)
{
$lon = Get-Random -Minimum -180 -Maximum 180
$lat = Get-Random -Minimum -90 -Maximum 90
$insertCommand = $connection.CreateCommand()
$insertCommand.CommandText = @"
INSERT INTO testtable(name, geoloc)
VALUES ('name $i', ST_GeomFromGML('<gml:Point xmlns:gml="http://www.opengis.net/gml">
<gml:coordinates decimal="." cs="," ts=" ">$lon,$lat </gml:coordinates>
</gml:Point>', 4326)
);
"@
$insertCommand.ExecuteNonQuery() | Out-Null
}

$connection.Close()

Now, we can go back to geoserver and add a postgis store.

geoserver-postgis-selection

The following parameters are important.

geoserver-postgis-details

After saving the store geoserver offers the option to publish a layer for it and that is what we want. To make things more interesting we can make a new group which combines this layer with the previously created group for the countries.

geoserver-postgis-result

There we are. The fancy red dots are the random points from the postgresql database.

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: