2

I'm trying to create a GeoJSON REST service from a PostgreSQL connection. First I fetch GeoJSON as a string from PostgreSQL:

import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.PreparedStatement;

/**
 *
 * @author me
 */
public class App2 {

    /**
     * @param args the command line arguments
    */

    private final String url = "jdbc:postgresql://127.0.0.1:5444/mydb";
    private final String user = "myuser";
    private final String password = "mypass";


    /**
     * Connect to the PostgreSQL database
     *
     * @return a Connection object
     */
    public String connect() {
        Connection conn = null;
        String geojson = "abc";
        try (Connection con = DriverManager.getConnection(url, user, password);
                PreparedStatement pst = con.prepareStatement("select ST_AsGeoJSON(geom) from (select ST_MakeLine(geom) as geom from (SELECT ST_MakePoint(location_longitude,location_latitude) as geom FROM locations where location_fix_time between '2019-05-13 11:08:30' and '2019-05-13 11:08:40' order by location_fix_time) a) b;");
                ResultSet rs = pst.executeQuery()) {

            while (rs.next()) {
                geojson = rs.getString(1);
                return geojson;
            }

        }  catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return geojson;

    }   



    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        App2 app2 = new App2();
        app2.connect();
        String abc = app2.connect();
        System.out.println(abc);
    }

} 

Then I'm trying to place it as GeoJSON on a REST webservice:

@Path("Mypath")
public class MyPathResource {
    @GET
    @Produces("text/plain")
    public String getText() {
        return "My First RESTful Service....";
    }
    private static GeometryJSON creategeoJSON(boolean prettyPrint) {
        App2 app2 = new App2();
        String queryres = app2.connect();
        GeometryJSON g = new GeometryJSON();
        try {
            g.readLine(queryres);
            return g;
        }
        catch (IOException e) {
            System.out.println(e.getMessage());
        }
        return g;
    }

    @GET
    @Path("przejazd/{idpoj}")
    @Produces(MediaType.APPLICATION_JSON)
    //@Produces(MediaType.TEXT_PLAIN)
    public Response przejazdfetch(@PathParam("idpoj") int idpoj)  {
    //JSONObject jsono = createJSON(true);
        GeometryJSON gjson = creategeoJSON(true);

    //return Response.ok().entity(jsono.toString()).build();
        return Response.ok().entity(gjson.toString()).build();
    }
}

This doesn't work however and when I try to load the webservice at http://localhost:8080/MyFirstREST/webresources/Mypath/przejazd/1 I get an error:

SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data.

Any ideas how it can be fixed?

  • 3
    See the example from the doc. You would need to add extra information beside st_AsGeoJSON – JGH May 21 at 12:18
  • check that you've requested json and not xml in the request, use CURL or POSTMAN to test web services not a browser – Ian Turton May 21 at 13:53
  • I've used CURL and it returned org.geotools.geojson.geom.GeometryJSON@374c1e6d. So probably not what I wanted. How to fix it? – aretai May 21 at 14:05
  • 1
    You probably want to send back the JSON not the .toString output – Ian Turton May 21 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.