PostgreSQL, Postgis, ST_GeomFromGeoJSON and JSON-C

842 views
Skip to first unread message

Samuel ROZE

unread,
May 19, 2017, 1:33:20 PM5/19/17
to Google Cloud SQL discuss
I've being trying to use Google Cloud SQL PostgreSQL with Postgis. After experiencing the "missing uuid-ossp extension issue", I've been trying to use the "ST_GeomFromGeoJSON" function:

```
INSERT INTO "locations" ("id","datetime","travelIdentifier","point","accuracy","altitude","altitudeAccuracy","heading","speed","segment") VALUES (DEFAULT,'2017-05-19 17:17:33.022 +00:00','93d4bc6e-ea3e-46db-95e4-4f5ee450070a',ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-122.31259857,37.49677685]}'),5,0,-1,297.42,32.73,7) RETURNING *;

ERROR:  You need JSON-C for ST_GeomFromGeoJSON
```

Based on Postgis' documentation, JSON-C is a required dependency and I understood from various Search queries that it is why I would have this issue. Any idea how, if possible, can we get Postgres with JSON-C ?

Thank you very much,
Samuel.

Ankush Agarwal

unread,
May 19, 2017, 1:40:37 PM5/19/17
to google-cloud...@googlegroups.com
We have a feature request for this and this will help us prioritize.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/33ea88b1-e478-4ceb-a4d5-6b6251fa21db%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



--
Ankush Agarwal

Software Engineer, Cloud SQL
 
There are 10 types of people in this world - those who understand binary and those who don't

Michael Marcacci

unread,
Jul 4, 2017, 11:39:54 PM7/4/17
to Google Cloud SQL discuss
Do you have a link to this feature request by chance, or is it not public? I'd like to follow along, as working with GeoJSON is one of our top use cases for PostgreSQL+PostGIS.

Thanks,
Mike

Jacob Baskin

unread,
Jul 17, 2017, 1:07:56 PM7/17/17
to Google Cloud SQL discuss
I believe that this is a regression. ST_GeomFromGeoJSON has worked in the past for us. We too would love to see this bug fixed as soon as possible. Thanks!

Michael Marcacci

unread,
Jul 20, 2017, 9:08:41 AM7/20/17
to Google Cloud SQL discuss
I'd like to bump my request for an issue link, if it's possible. I am currently shimming the GeoJSON functions with some pretty dirty conversions through other formats, and would really like to clean this up as soon as this bug is fixed.

Thanks,
Mike

John Murphy

unread,
Sep 2, 2017, 3:53:24 PM9/2/17
to Google Cloud SQL discuss
I'm having the same issue.  Any update on this?

Tamal Mondal

unread,
Sep 27, 2017, 9:03:43 AM9/27/17
to Google Cloud SQL discuss
Any update ? till getting same issue.

Tamal Mondal

unread,
Sep 27, 2017, 9:03:43 AM9/27/17
to Google Cloud SQL discuss
Any update ?

Ben Calnan

unread,
Nov 22, 2017, 9:06:43 AM11/22/17
to Google Cloud SQL discuss
Just to echo, just wondering is there any update on this? 

DRIVEUP Developers

unread,
Nov 23, 2017, 11:49:21 AM11/23/17
to Google Cloud SQL discuss
Just to echo too. We are trying migrate to Cloud SQL but we need the ST_GeomFromGeoJSON().
Thank you!

Pierre Pongi

unread,
Dec 7, 2017, 8:56:44 AM12/7/17
to Google Cloud SQL discuss
We're having the same issue. Any update?

Julian Guinard

unread,
Dec 7, 2017, 8:56:51 AM12/7/17
to Google Cloud SQL discuss
Any update on this please? We would love to be able to use this PostGIS feature too

Thanks!


Le vendredi 19 mai 2017 19:33:20 UTC+2, Samuel ROZE a écrit :

Xavier HAUSHERR

unread,
Dec 7, 2017, 8:57:03 AM12/7/17
to Google Cloud SQL discuss
Hi Google team,

We are needing this feature to migrate our application on Google Cloud. Our only alternative si to create ourselves a Postgres cluster, but I prefer to use Cloud SQL.

I hope you can prioritize this important feature.

Thanks

Stephen ZAMBAUX

unread,
Dec 27, 2017, 12:23:53 PM12/27/17
to Google Cloud SQL discuss
Same issue, I am under migration :(


Le vendredi 19 mai 2017 19:33:20 UTC+2, Samuel ROZE a écrit :

Mauro Pompilio

unread,
Jan 8, 2018, 1:54:50 PM1/8/18
to Google Cloud SQL discuss
Hello Ankush, can you please provide an update on this? I just stumbled upon the same issue, will it get fixed any time soon?

Mauro Pompilio

unread,
Jan 8, 2018, 1:55:45 PM1/8/18
to Google Cloud SQL discuss
Hi Michael,

Would be possible for you to post a gist with the shimming of the GeoJSON functions? is that workaround still working for you?


Thanks,
Mauro.

Michael Marcacci

unread,
Jan 8, 2018, 2:09:44 PM1/8/18
to Google Cloud SQL discuss
Hi Mauro,

Absolutely! The backend that runs these queries is written in javascript, and we use the wkx library to do these conversions. Our code is littered with these conversions right now, but here's a simple model (using a custom modeling system) that should illustrate what we have to do:


Note the commented-out section that reference this thread. This uses GeoJSON and works everywhere except Google Cloud. Having the models as an abstraction layer means that we've been able to isolate the inconvenience to some extent, but they really muck up analytics queries.

I know this product is still in beta, but the lack of response to this issue is becoming a bit frustrating; a simple "we're still working on it" would go a long way.

Cheers,
Mike

Michael Marcacci

unread,
Jan 9, 2018, 1:33:37 PM1/9/18
to Google Cloud SQL discuss
Well, now that I shared my workaround I found a nice production bug that it introduces. It turns out that the conversion reduces the numerical precision by one bit... so -85.39986445542691 becomes -85.3998644554269 (missing the last digit there).

This has broken the idempotent guarantee of our API, since the comparison is done application-side. Thus, a second identical request can look different and be rejected.

I'm going to create another request, this thread is clearly not getting noticed. I'll post any updates here.

Michael Marcacci

unread,
Jan 9, 2018, 1:41:46 PM1/9/18
to Google Cloud SQL discuss
OK, it looks like there is actually an issue tracking this: https://issuetracker.google.com/issues/37302950
Reply all
Reply to author
Forward
0 new messages