PostGIS one to many relationship without duplicate geometries

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

PostGIS one to many relationship without duplicate geometries

Hugo Paul-3

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
[hidden email]
You can UNSUBSCRIBE at https://lists.osgeo.org/mailman/listinfo/africa
Reply | Threaded
Open this post in threaded view
|

Re: PostGIS one to many relationship without duplicate geometries

Gavin Fleming-4
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. 

regards

Gavin
--
Gavin Fleming - Joint MD - PrGISc [PGP1234]
Skype: phlemingo
Office: +27(0)878092702
------------------------------------------------------------------------------------------
Visit 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 <[hidden email]> 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
[hidden email]
You can UNSUBSCRIBE at https://lists.osgeo.org/mailman/listinfo/africa


_______________________________________________
Africa mailing list
[hidden email]
You can UNSUBSCRIBE at https://lists.osgeo.org/mailman/listinfo/africa