How to convert data from PostGIS database into GeoJSON in Java

Question

I have a PostgreSQL/PostGIS database and I want to convert my data table from database into GeoJSON format.

My purpose is to use this GeoJSON for creating a map with JavaScript. I am using Java and JDBC in Spring MVC. What is the best way to convert data?

Answer 1

This can be done in the query that pulls the data out of the database. You can use the postgis function ST_AsGeoJSON(). Here is a link to the documentation for it.

https://postgis.net/docs/ST_AsGeoJSON.html

One thing to note is that the result of ST_AsGeoJSON() only returns the geometry portion of the data. If you need to get a geojson feature, then you will have to create the feature object and add the geometry to it.

An example of the result that you should expect when using ST_AsGeoJSON() would be {"type": "Point", "coordinates": [12, 15]}.

If you wanted to create a feature out of the geometry object, that would look something like {"type": "Feature", "properties": {}, "geometry": {"type": "Point", "coordinates": [12, 15]}}.

Answer 2

ogr2ogr is your friend. Steve Bennett gives an excellent example with custom properties/columns selection here, along the lines of this:

ogr2ogr -f GeoJSON out.json "PG:host=localhost dbname=mydb user=myuser password=mypw" -sql "SELECT column1, column2, column3 FROM mytable"