[OSGeo Africa] PostGIS one to many relationship without duplicate geometries

Gavin Fleming gavin at kartoza.com
Mon Aug 17 23:10:06 PDT 2020

Hi Hugo, nice to hear from you

Just exclude the geometry from the list of fields in the select statement, otherwise, by definition, it will be repeated. 

If you simply want to define the relationship to enforce referential integrity then create a foreign key.

Hope this helps. If you need professional support or consulting, Kartoza does offer these. See the options in the Solutions section of our website of contact me directly. 


Gavin Fleming - Joint MD - PrGISc [PGP1234]
Skype: phlemingo
Office: +27(0)878092702
Schedule a meeting <https://calendly.com/gavin-kartoza>

Visit http://kartoza.com <http://kartoza.com/> to find out about open source:
* Full stack web, desktop and mobile geospatial software development
* GIS training
* Support, hosting and maintenance
* Consulting services

> On 17 Aug 2020, at 21:06, Hugo Paul <hugopaul at live.com> wrote:
> Hi Everyone 
> I am looking for some guidance/assistance please. I am building a web app using ArcGIS JS API 4.16 and our GeoDBMS is Postgres with PostGIS layers served through Geoserver 2.17. 
> We do not have an Arc Enterprise license and therefor use the Geoserver, OGCFeatureLayer, wms, etc. method to load layers into our web app.  
> My problem is that when I want to make a one to many relationship in Postgres (where the geometry layer is the one and a non-spatial table is the many), it duplicates the geometry.  
> Example: I have one high rise apartment block (1 polygon) with 50 living units (non-spatial table) when I try to relate (join) the non-spatial table to the spatial table it creates 50 duplicate polygons. I do this using views.. 
> All I want is a relationship between the spatial and non-spatial tables without creating duplicate geometries (Similar to the ArcGIS relate function). 
> Has anyone experienced this that might be able to give me some advise please? Or if anyone knows of a consultancy firm with PostGIS experience who might be able to assist, please advise. 
> Thank you in advance 
> Kind regards 
> Hugo 
> _______________________________________________
> Africa mailing list
> Africa at lists.osgeo.org <mailto:Africa at lists.osgeo.org>
> You can UNSUBSCRIBE at https://lists.osgeo.org/mailman/listinfo/africa <https://lists.osgeo.org/mailman/listinfo/africa>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/africa/attachments/20200818/d59d45f2/attachment.html>

More information about the Africa mailing list