How to generate map data from PostGIS or shapefiles for use in reports

Introduction

In this article we show one way to load a shapefile into the PostgreSQL database (with PostGIS extension) and then query the database in a way that can be used inside a report with a Map component. An example of using the polygons from the shapefile in a map component is How to use TIBCO® Maps with TIBCO Jaspersoft® Studio Pro. A similar approach could be used with Google Maps, etc.

Downloading sample data

In this example we will use a shapefile with coordinates of the US States from the US Census Tiger project, but you can obviously load data for any geographical areas you are interested in, as long as you have the data ; )

Download and unzip this file: http://www2.census.gov/geo/tiger/TIGER2015/STATE/tl_2015_us_state.zip

Other files are available here: http://www.census.gov/cgi-bin/geo/shapefiles/index.php

Loading data

Install then enable PostGIS on your target database
Enter the directory created when unzipping the archive earlier and convert the shapefile to SQL (the -s option might not be required), then run it in your target database:
```
shp2pgsql -I -s 94269 tl_2015_us_state.shp usa_states > shape.sql
```
Create the PL/pgSQL function ST_Vectorize that will be used to transform the GIS table in a "plain" table – attached to this article as proc.sql_0.zip. This is a modified version of the function found here (thanks Simon Greener)
Create table to store "final" data – attached as ddl.sql.zip. This is not required but it is recommended for performance – you could use a database view instead but the performance might not be ideal. The query in ddl.sql uses a WHERE condition with "seq % 20 = 0" to sample the source data at 5%. If you want to increase precision (and load on the server and on the client when rendering the map), reduce 20 to a smaller number, e.g. 8 would give you a 12.5% sample, 2 would give you a 50% sample, etc.

Running query

This is a sample query you might run inside your report (statepop.sql.zip):

SELECT usa_state_population.population,
gis_usa_states.state_name,
gis_usa_states.seq,
gis_usa_states.latitude,
gis_usa_states.longitude
FROM gis_usa_states
INNER JOIN usa_state_population ON
  usa_state_population.state_name = gis_usa_states.state_name
ORDER BY state_name, seq;

Please be aware that an appropriate ORDER BY is usually important to render the polygons correctly.

In particular you will want to order first by the main geographical entity (e.g. US State in this case) and then by a sequential/unique number or identifier (that would ideally follow the path creating the polygon).