Xata•2mo ago

Working with Postgis

Hi all. I'm looking to work with Postgis, in order to store event location data and return search results based upon proximity to device geolocation and Google Maps data. Web, docs, Xata bot and Discord searches are all turning up a blank. Not sure where to begin, or if I should be considering a different approach? Any guidance to get me started appreciated.

4 Replies

daz.devOP•2mo ago

Okay, got it. Eventually found that it's preinstalled from this docs section, and could verify preinstallation with

SELECT * FROM pg_available_extensions WHERE name = 'postgis';

SELECT * FROM pg_available_extensions WHERE name = 'postgis';

Connect to Postgres

Learn how to connect to Postgres directly via the wire protocol

daz.devOP•2mo ago

However I'm still not entirely unblocked. Whenever I run a simple test query such as:

SELECT ST_Distance(
  ST_MakePoint(-74.0060, 40.7128)::geography, 
  ST_MakePoint(-73.9857, 40.7484)::geography
);

SELECT ST_Distance(
  ST_MakePoint(-74.0060, 40.7128)::geography, 
  ST_MakePoint(-73.9857, 40.7484)::geography
);

I get the response of Error: invalid SQL: function [st_distance] is unknown or not allowed Running SELECT * FROM pg_available_extensions WHERE installed_version IS NULL; returns that I have postgis, and many accompanying packages uninstalled. When I try to activate Postgis with CREATE EXTENSION postgis;, I get the error

invalid SQL: unsupported statement [CREATE EXTENSION] on strict_with_ddl level, see https://xata.io/sql-support for the support matrix

Going to pause here and switch tasks, but my understanding is that postgis should be active as part of the default shared cluster setup. However I can't activate this, or access the functionality with my current setup/understanding. Any guidance much appreciated!

cmck•2mo ago

Hi there, thanks for bringing this up, I'll take a look and get back to you shortly. Hi thanks for bearing with me. So, Postgis was originally shipped with our shared cluster but we had to disable it due to several issues that arose. It's currently only available on dedicated clusters. If you need this extension for your application then I would not recommend using Xata for the time being. Apologies for any misunderstanding, our documentation needs to be updated to reflect the above. Please let me know if there's anything else I can help you with.

daz.devOP•2mo ago

Oh. I'm 300 hours into a build with the Xata Client as the data transport library, moving off isn't an attractive proposition now. Appreciate the confirmation, I'll have to give this some thought. Thanks.

Gaming

Programming

Working with Postgis

Did you find this page helpful?