Postgres: Support Postgis Point type and related functions. #5602

vanniktech · 2025-01-04T09:56:36Z

Still have a bunch of questions and this is not done yet. However, I did manage to publish the postgres dialect into my maven local and use it already. Since my query filtering is dynamic, I currently only need the Geometry Type since I construct the sql to query myself.

Is it correct that I when I make a change to the dialect, I have to ./gradlew publishToMavenLocal? This moves it into the build directory of the repository. So like in sqldelight/dialects/postgresql, I execute ./gradlew publishToMavenLocal and then I can run the integration tests?

How do I run the integration tests though? If I'm in the sqldelight/sqldelight-gradle-plugin/src/test/integration-postgresql directory and I try to run the tests using ./gradlew test, I'm getting:

PostgreSqlTest > testBooleans FAILED
    java.lang.IllegalStateException: Previous attempts to find a Docker environment failed. Will not retry. Please see logs and check configuration
        at org.testcontainers.dockerclient.DockerClientProviderStrategy.getFirstValidStrategy(DockerClientProviderStrategy.java:232)
        at org.testcontainers.DockerClientFactory.getOrInitializeStrategy(DockerClientFactory.java:154)
        at org.testcontainers.DockerClientFactory.dockerHostIpAddress(DockerClientFactory.java:344)
        at org.testcontainers.containers.ContainerState.getHost(ContainerState.java:64)
        at org.testcontainers.containers.PostgreSQLContainer.getJdbcUrl(PostgreSQLContainer.java:100)
        at org.testcontainers.containers.JdbcDatabaseContainer.constructUrlForConnection(JdbcDatabaseContainer.java:309)
        at org.testcontainers.containers.JdbcDatabaseContainer.createConnection(JdbcDatabaseContainer.java:269)
        at org.testcontainers.jdbc.ContainerDatabaseDriver.connect(ContainerDatabaseDriver.java:140)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:253)
        at app.cash.sqldelight.postgresql.integration.PostgreSqlTest.<init>(PostgreSqlTest.kt:24)

What docker do I need to start? Could I also just point it to a local postgres installation on my Mac?

dialects/postgresql/src/main/kotlin/app/cash/sqldelight/dialects/postgresql/PostgreSqlType.kt

...ts/postgresql/src/main/kotlin/app/cash/sqldelight/dialects/postgresql/grammar/PostgreSql.bnf

gradle/libs.versions.toml

griffio · 2025-01-05T19:44:20Z

Still have a bunch of questions and this is not done yet. However, I did manage to publish the postgres dialect into my maven local and use it already. Since my query filtering is dynamic, I currently only need the Geometry Type since I construct the sql to query myself.

Is it correct that I when I make a change to the dialect, I have to ./gradlew publishToMavenLocal? This moves it into the build directory of the repository. So like in sqldelight/dialects/postgresql, I execute ./gradlew publishToMavenLocal and then I can run the integration tests?

How do I run the integration tests though? If I'm in the sqldelight/sqldelight-gradle-plugin/src/test/integration-postgresql directory and I try to run the tests using ./gradlew test, I'm getting:
PostgreSqlTest > testBooleans FAILED
    java.lang.IllegalStateException: Previous attempts to find a Docker environment failed. Will not retry. Please see logs and check configuration
        at org.testcontainers.dockerclient.DockerClientProviderStrategy.getFirstValidStrategy(DockerClientProviderStrategy.java:232)
        at org.testcontainers.DockerClientFactory.getOrInitializeStrategy(DockerClientFactory.java:154)
        at org.testcontainers.DockerClientFactory.dockerHostIpAddress(DockerClientFactory.java:344)
        at org.testcontainers.containers.ContainerState.getHost(ContainerState.java:64)
        at org.testcontainers.containers.PostgreSQLContainer.getJdbcUrl(PostgreSQLContainer.java:100)
        at org.testcontainers.containers.JdbcDatabaseContainer.constructUrlForConnection(JdbcDatabaseContainer.java:309)
        at org.testcontainers.containers.JdbcDatabaseContainer.createConnection(JdbcDatabaseContainer.java:269)
        at org.testcontainers.jdbc.ContainerDatabaseDriver.connect(ContainerDatabaseDriver.java:140)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:253)
        at app.cash.sqldelight.postgresql.integration.PostgreSqlTest.<init>(PostgreSqlTest.kt:24)
What docker do I need to start? Could I also just point it to a local postgres installation on my Mac?

Is it correct that I when I make a change to the dialect, I have to ./gradlew publishToMavenLocal?This moves it into the build directory of the repository. So like in sqldelight/dialects/postgresql, I execute ./gradlew publishToMavenLocal and then I can run the integration tests?

No need to publishToMavenLocal for changes to dialect or running integration tests

How do I run the integration tests though?

Something like - :sqldelight-gradle-plugin:dockerTest --tests "app.cash.sqldelight.dialect.DialectIntegrationTests.integrationTestsPostgreSql"

What docker do I need to start? Could I also just point it to a local postgres installation on my Mac?

Use Docker Desktop or Docker Daemon running locally - the integration tests use TestContainers see app.cash.sqldelight.postgresql.integration.async.PostgreSqlTest

Also, because PostGis is a third party extension - it would need to be installed when the integration tests run as PostGis types do not exist in the docker postgresql database.

vanniktech · 2025-01-06T01:30:12Z

@AlecKazakova how should I continue with this? Is postgis first party support in postgres desired or not? I think it'd be cool.

vanniktech · 2025-01-06T04:53:47Z

Also, because PostGis is a third party extension - it would need to be installed when the integration tests run as PostGis types do not exist in the docker postgresql database.

@griffio where can I do this? I've tried to find some kind of docker configuration file or something but came up emtpy.

griffio · 2025-01-06T09:06:43Z

Also, because PostGis is a third party extension - it would need to be installed when the integration tests run as PostGis types do not exist in the docker postgresql database.

@griffio where can I do this? I've tried to find some kind of docker configuration file or something but came up emtpy.

It's quite auto-magical -

sqldelight/sqldelight-gradle-plugin/src/test/integration-postgresql/src/test/kotlin/app/cash/sqldelight/postgresql/integration/PostgreSqlTest.kt

Line 24 in f30f3d3

val conn = DriverManager.getConnection("jdbc:tc:postgresql:latest:///my_db")

The jdbc driver url will cause the TestContainer runtime on the classpath to load the docker image jdbc:tc:postgresql:latest - see https://java.testcontainers.org/modules/databases/jdbc/

You must have a Docker daemon running otherwise you get the Previous attempts to find a Docker environment failed

Also you may be able to load the extension here ->

sqldelight/sqldelight-gradle-plugin/src/test/integration-postgresql/src/test/kotlin/app/cash/sqldelight/postgresql/integration/PostgreSqlTest.kt

Line 65 in f30f3d3

@Before fun before() {

...postgresql/src/main/kotlin/app/cash/sqldelight/dialects/postgresql/PostgreSqlTypeResolver.kt

vanniktech · 2025-01-11T11:53:55Z

Okay so this thing is finally green now with all the required tests for the newly added syntax. Without @griffio's help I would have never come this far (because I've never touched those grammar files before).

The Sqldelight postgres dialect now supports Postgis; not the entire syntax is supported but everything you need for inserting points and querying by them. That allows you to basically have locations stored in the database and efficiently query for distances from one location to another. Adding more things is trivial now, since the foundation has been laid out.

I'd argue for having this included in the default postgres grammar because:

the added grammar is trivial
it's really cool to get out of the box support for this with the sqldelight postgres dialect
we don't need to do special dances to integration test this since there's a docker image with postgis!
the overload of having this as a separate dialect and published as a separate artifact is way higher than just me being responsible for this now

griffio · 2025-01-13T09:51:50Z

...postgresql/src/main/kotlin/app/cash/sqldelight/dialects/postgresql/PostgreSqlTypeResolver.kt

@@ -244,6 +250,12 @@ class PostgreSqlTypeResolver(private val parentResolver: TypeResolver) : TypeRes
      is PostgreSqlDoubleColonCastOperatorExpression -> {
        (argument.parent.parent as SqlExpr).postgreSqlType()
      }
+      is PostgreSqlGeometrySetsridFunctionStmt -> {
+        IntermediateType(INTEGER)


This must be changed from IntermediateType(INTEGER) -> PostgreSqlType.INTEGER as the parent resolver maps INTEGER to LONG and the function fails at runtime as Postgresql ST_SetSRID as it only takes Int

griffio · 2025-01-13T09:53:50Z

...gration-postgresql/src/main/sqldelight/app/cash/sqldelight/postgresql/integration/Postgis.sq

+
+insertMakePoint:
+INSERT INTO locations (name, point)
+VALUES ('New York', ST_SetSRID(ST_MakePoint(:x::REAL, :y::REAL), :srid::INTEGER));


Test with VALUES ('New York', ST_SetSRID(ST_MakePoint(:x, :y), :srid)); This checks the resolver works without casts - as per fix above changing from LONG -> INT

I did change this:

together with:

but this fails for me:

> A failure occurred while executing app.cash.sqldelight.gradle.SqlDelightTask$GenerateInterfaces > Failed to compile PostgreSqlInsertStmtImpl(INSERT_STMT): [] : INSERT INTO locations (name, point) VALUES ('New York', ST_SetSRID(ST_MakePoint(:x::REAL, :y::REAL), :srid))

I will have a look - with the bind resolver the casting is not required as sets to variables to double for ST_MakePoint and int for ST_SetSRID

INSERT INTO locations (name, point) VALUES ('New York', ST_SetSRID(ST_MakePoint(:x, :y), :srid))

This compiles

Until we find a better way with function argument resolution - It's best to avoid mixing casts and non casted variables for the ST_* functions - either use all casts or none

Using casts turn the statement into an anonymous FunctionExpression and needs a casted variable to tell it the type - hence the error Cannot bind unknown types or null

griffio · 2025-01-13T09:56:33Z

...gration-postgresql/src/main/sqldelight/app/cash/sqldelight/postgresql/integration/Postgis.sq

+
+insertMakePointM:
+INSERT INTO locations (name, pointM)
+VALUES ('New York', ST_SetSRID(ST_MakePointM(:x::REAL, :y::REAL, :m::REAL), :srid::INTEGER));


After testing locally - I think that all the ::REAL must be ::FLOAT

50 | New York | 0101000020E610000000000040628052C0000000E0225B4440 - inserted with REAL
51 | New York | 0101000020E6100000AAF1D24D628052C04260E5D0225B4440 - inserted with FLOAT

It is missing the precision and would cause tests to be invalid

REAL in PostgreSQL is a 4-byte single-precision floating-point number
FLOAT without precision specified defaults to 8-byte double-precision floating-point number
The first insert using REAL (4 bytes) has less precision, which is why you see the coordinates rounded/truncated in the first row. The second insert using FLOAT (8 bytes) maintains more decimal precision, visible in the second row's coordinates.

For geospatial coordinates where precision matters, using FLOAT (double precision) is the better choice since it provides more accurate coordinate storage. This explains why you see slightly different binary representations of the point in the database

Confusingly - REAL is mapped to double in the resolver type code and would work if set by the resolver
When using the cast ::REAL even though JDBC set it as double the value is without the full precision

griffio · 2025-01-13T09:58:05Z

...tion-postgresql/src/test/kotlin/app/cash/sqldelight/postgresql/integration/PostgreSqlTest.kt

+    }
+
+    with(database.postgisQueries.selectLocationByDistanceHardcoded(distanceMeters = 0.0).executeAsList()) {
+      assertThat(size).isEqualTo(0)


This should be size 1 but is zero size because of the precision issue with REAL vs FLOAT

griffio

See comments for some issues

swankjesse

Looks legit

vanniktech commented Jan 4, 2025

View reviewed changes

vanniktech added 2 commits January 6, 2025 11:45

Postgres: Preliminary Postgis support.

b99b740

Use String for the actual type and provide Adapter.

9b4b7af

vanniktech force-pushed the postgres-postgis branch from 9a86b74 to 9b4b7af Compare January 6, 2025 04:52

vanniktech added 4 commits January 6, 2025 17:29

Try to create postgis extension and switch to postgis.

c8d39e1

Spotless.

0717945

Fix unit test.

b877a1b

Support ST_SetSRID & ST_MakePoint.

43cfdfc

vanniktech commented Jan 7, 2025

View reviewed changes

...postgresql/src/main/kotlin/app/cash/sqldelight/dialects/postgresql/PostgreSqlTypeResolver.kt Outdated Show resolved Hide resolved

...postgresql/src/main/kotlin/app/cash/sqldelight/dialects/postgresql/PostgreSqlTypeResolver.kt Outdated Show resolved Hide resolved

vanniktech and others added 5 commits January 7, 2025 16:20

Merge branch 'master' into postgres-postgis

0fcea96

Add more queries.

2a734e8

Grammar and Resolver

8127bad

Tests

91352eb

Merge pull request #1 from griffio/postgres-postgis

c7622ef

vanniktech force-pushed the postgres-postgis branch 4 times, most recently from ddbb0a1 to 42a02b2 Compare January 11, 2025 10:46

More tests.

2da10d2

vanniktech force-pushed the postgres-postgis branch from 42a02b2 to 2da10d2 Compare January 11, 2025 11:15

vanniktech marked this pull request as ready for review January 11, 2025 11:53

vanniktech requested a review from griffio January 11, 2025 11:54

vanniktech changed the title ~~Postgres: Preliminary Postgis support.~~ Postgres: Support Postgis Point type and related functions. Jan 11, 2025

griffio reviewed Jan 13, 2025

View reviewed changes

griffio suggested changes Jan 13, 2025

View reviewed changes

Use float.

95b67fb

griffio approved these changes Jan 13, 2025

View reviewed changes

swankjesse approved these changes Jan 13, 2025

View reviewed changes

hfhbd self-requested a review January 14, 2025 20:42

griffio mentioned this pull request Jan 22, 2025

Fix SqlDelightModule type resolver order #5625

Merged

griffio mentioned this pull request Mar 31, 2025

Support SqlDelight modules to implement PostgreSql extensions #5677

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Postgres: Support Postgis Point type and related functions. #5602

Postgres: Support Postgis Point type and related functions. #5602

vanniktech commented Jan 4, 2025

griffio commented Jan 5, 2025 •

edited

Loading

vanniktech commented Jan 6, 2025

vanniktech commented Jan 6, 2025

griffio commented Jan 6, 2025 •

edited

Loading

vanniktech commented Jan 11, 2025

griffio Jan 13, 2025

griffio Jan 13, 2025

vanniktech Jan 13, 2025

griffio Jan 13, 2025 •

edited

Loading

griffio Jan 13, 2025

griffio Jan 13, 2025 •

edited

Loading

griffio Jan 13, 2025

griffio left a comment

swankjesse left a comment

Postgres: Support Postgis Point type and related functions. #5602

Are you sure you want to change the base?

Postgres: Support Postgis Point type and related functions. #5602

Conversation

vanniktech commented Jan 4, 2025

griffio commented Jan 5, 2025 • edited Loading

vanniktech commented Jan 6, 2025

vanniktech commented Jan 6, 2025

griffio commented Jan 6, 2025 • edited Loading

vanniktech commented Jan 11, 2025

griffio Jan 13, 2025

Choose a reason for hiding this comment

griffio Jan 13, 2025

Choose a reason for hiding this comment

vanniktech Jan 13, 2025

Choose a reason for hiding this comment

griffio Jan 13, 2025 • edited Loading

Choose a reason for hiding this comment

griffio Jan 13, 2025

Choose a reason for hiding this comment

griffio Jan 13, 2025 • edited Loading

Choose a reason for hiding this comment

griffio Jan 13, 2025

Choose a reason for hiding this comment

griffio left a comment

Choose a reason for hiding this comment

swankjesse left a comment

Choose a reason for hiding this comment

griffio commented Jan 5, 2025 •

edited

Loading

griffio commented Jan 6, 2025 •

edited

Loading

griffio Jan 13, 2025 •

edited

Loading

griffio Jan 13, 2025 •

edited

Loading