Support for PostGIS extension

[a777med]

Hi

Are the currently supported Postgres functions by Doltgresql enough for using the postgis extension?

Thanks

[fulghum]

Hi @a777med, thanks for checking out Doltgres. We don't support any Postgres extensions yet, but we are planning on prioritizing some of the most popular extensions and implementing them, based on what customers tell us they need. PostGIS is definitely on our radar. PostGIS has a pretty large surface area, so it will be really helpful for us to know what parts of the PostGIS API are the most important for customers, so we can prioritize those areas first.

[palmerj]

Would be very keen with this as well.

Yes I also agree PostGIS is a very large project. My priority would be to support storage of the geometry data type and the support functions that would allow loading of geospatial data into the database. GDAL/OGR with the basic options would be good place to start and ensuring all the functions use there are supported.

An example load would be:

ogr2ogr \
  -f PostgreSQL \
  PG:"host=localhost dbname=doltdemo user=postgres password=passowrd" \
  my_shapes.shp \
  -nln my_spatial_table \        # table name
  -lco GEOMETRY_NAME=shape \     # column name
  -nlt MULTIPOLYGON \            # force type
  -t_srs EPSG:4167 \             # makes the SRID explicit
  -lco SPATIAL_INDEX=NO \        # avoids the need for GiST support
  --config PG_USE_COPY YES       # keeps to the fast COPY code-path

My quick analyse is the following functions are needed:

Layer	What GDAL touches	Why it is needed	Minimal implementation work
Handshake	postgis_version()	The driver calls it immediately after connecting to decide it is talking to a “PostGIS-like” DB.	Stub SQL function should be enough.
Type system	geometry base type with typmod geometry( subtype , SRID [, dims] )	Used in the CREATE TABLE … geometry(…) clause that ogr2ogr emits.	typmod parser to store (typeid, srid, dims).* geometry_in/out (WKT / EWKT text) and geometry_recv/send (binary) able to understand EWKB.
Constructors	ST_GeomFromEWKB(bytea)	Required only on the INSERT code-path, but convenient for users.	Wraps geometry_recv internally.
	ST_SetSRID(geometry,int)	Only used if source data lack an SRID.	Simple update of the internal SRID field.
Serialisers	Hex EWKB in COPY	COPY sends ‘\x…’ hex EWKB strings; handled by geometry_recv.	Already covered by the binary I/O routines above I think
Meta-data (nice to have)	spatial_ref_sys table, geometry_columns view	I don't ogr2ogr does not require these to load data, but will need a double check. Ideally just add them, the view should be ok to create. The spatial_ref_sys can be empty to start with to make in simple	Seed with SRID 4167 row; maintain triggers to keep geometry_columns coherent.
Optional extras	GiST opclass & CREATE INDEX … USING GIST	Only needed if you want -lco SPATIAL_INDEX=YES later on.	Bounding-box operators, GiST support methods, an analyse function.

Once that was done it would be nice to support getting the spatial data back out with reading info type queries with ogrinfo and ogr2ogr:

catalogue query & typmod parsing:

• postgis_typmod_dims(geometry)
• postgis_typmod_srid(geometry)
• postgis_typmod_type(geometry)

SRID helpers

• ST_SetSRID(geometry,int)
• ST_SRID(geometry)

Bounding boxes

• ST_EstimatedExtent(schema,table,col)
• ST_Extent(geometry)
• ST_Intersects(geometry, geometry)
• ST_makeenvelope

Geometry Type/structure introspection

• GeometryType(geometry
• ST_NumGeometries(geometry)
• ST_Zmflag(geometry)

PostgreSQL utility functions

• encode(bytea, text)