Search code examples
javajpapostgis

Java with PostGIS "ERROR: function st_makepoint(double precision, double precision) does not exist" but query works on SQL

I am trying to make a query through my JPA repository but I'm getting the following error:

ERROR: function st_makepoint(double precision, double precision) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 83

CompanyRepository

@Query(value = "SELECT c.* FROM company c JOIN store s ON c.store_id = s.id " +
            "WHERE ST_Within(ST_MakePoint(s.longitude, s.latitude), :polygon)",
            nativeQuery = true)
    List<CompanyEntity> findStoresInPolygon(@Param("polygon") Geometry polygon);

If I try to make the same query on SQL it works as expected. (I have a list of stores saved and only a couple of them fall within that polygon shown below, and those are the ones being retrieved)

SELECT c.*
FROM mySchema.company c
JOIN mySchema.store s ON c.store_id = s.id
WHERE ST_Within(
    ST_MakePoint(s.longitude , s.latitude),
    'POLYGON((10.7522 59.9139, 5.3221 60.3913, 10.3951 63.4305, 5.7331 58.969, 10.7522 59.9139))'
);

Thanks in advance!

OTHER POSSIBLE REVELANT INFO:

Not sure if it's relevant but latitude and longitude are being stored like shown below:

StoreEntity

@Column(nullable = false)
private Double latitude;

@Column(nullable = false)
private Double longitude;

The schema is defined in the application.properties and other queries work, I'm only having a problem with the ST_MakePoint.

I've read a bunch of other topics not only here in stackoverflow, official PostGIS docs, but also on other websites as well and tried different alternatives, if anyone have any sugestion I appreciate it.

Some of the things tried and did not work include cast lat and long to double and numeric, put coordinates directly on the ST_MakePoint.

I am expecting for the results from Java to be the same as the ones from SQL query.

(the posts stakoverflow have as possible duplicates have differences and I've already tested for what was different)

Solution

  • I solved the problem by adding the public to my current schema in the application.properties file

    spring.datasource.url=jdbc:postgresql://localhost:5433/myCustomdb?currentSchema=customname,public