How to convert data from a PostGIS database to GeoJSON in Java

Question

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

My goal is to use this GeoJSON to create a map with JavaScript. I use Java and JDBC in Spring MVC. What is the best way to convert data?

Answer 1

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

http://postgis.org/docs/ST_AsGeoJSON.html

, ST_AsGeoJSON() . geojson, .

, ST_AsGeoJSON(), {"type": "Point", "coordinates": [12, 15]}.

, {"type": "Feature", "properties": {}, "geometry": {"type": "Point", "coordinates": [12, 15]}}.

Answer 2

ogr2ogr - . / , :

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

Answer 3

ST_AsGeoJSON . , , , , , GeoJSON , Well Known Binary (EWKB), Postgres . , - MVC- JTS.

( ), JPA (, Hibernate 5 Hibernate-Spatial 4), JTS ( ) . WKB (Well Known Binary) , Java, , 64 (8 ) - ! Hibernate ( , SRID ..):

@Column(columnDefinition = "geometry(MultiPolygon,4326)")
private Geometry geom;

Spring, PostGIS application.properties( ):

spring.jpa.database-platform=org.hibernate.spatial.dialect.postgis.PostgisDialect

Spring ( JPQL , , ..).

, GeoJSON Jackson , JTS, https://github.com/bedatadriven/jackson-datatype-jts:

<dependency>
  <groupId>com.bedatadriven</groupId>
  <artifactId>jackson-datatype-jts</artifactId>
  <version>2.2</version>
</dependency>

Jackson, Spring, bean , :

    @Bean
    public Jackson2ObjectMapperBuilder objectMapperBuilder() {
            Jackson2ObjectMapperBuilder builder = new Jackson2ObjectMapperBuilder();
            builder.modulesToInstall(new JtsModule());
            return builder;
    }

Jackson, ( , ..), .

Now, when you return the JTS geometry to an object from your Spring MVC controllers, they are automatically converted to GeoJSON geometry. You will have to either replicate the GeoJSON Feature / FeatureCollection structures as classes if you want to use them, or return something (like your Entity) that can easily be mapped to JavaScript in such a structure.