Join - One to many

[Ernie Johnson]

Hello Everyone,

 

I am having a problem joining a text file to my vector cadastre lot layer and was hoping someone could help.  We are identifying lots that have multiple zones assigned to them.  Usually, they are overlaps where the cadastre layer and the zoning layer don't match up exactly. 

 

We have our cadastre layer with all the lots in it, and we have a text file that lists all the lots that have multiple zoning associated with it.  All of our cadastre lots have a unique PIN number assigned to them.  The text file also has these PIN numbers, and that is what I'm using for the join.  However, the text file has multiple records for some of the lots (where there is more than just one overlapping zone) and we need to keep those records separate.  So, essentially I need to do a one to many join.  When the join is performed, only the first unique record from the text file is joined to the vector layer, so I am ending up with many lost records.

 

I can set up a relation, however this only works for individual searches.  Also, the relation is saved in the project and not in the attribute table or a new shapefile, so I can't do analysis with it.

 

I tried reversing the join (joining the vector layer attribute table to the text file).  That keeps all the records, but now there is no spatial component to the data.

 

Has anyone had to do a many to one join before in QGIS?  There are ways to do it in ArcMap and MapInfo, but I would rather stick with QGIS.

 

Regards,

Ernie

[Thomas McAdam]

Hi Ernie

I am not sure about using the join on the layer properties for this, I haven't used it all that much.

One alternative is to put both sources (the shp and txt) into a spatialite database. Using the QspatiaLite plugin it is really easy to create a blank database and import shape and text files as tables. Then you can do whatever you like in terms of joins, and use it in QGIS as a view.

Regards

Tom 

[Barrett Higman]

Hi Ernie,

 

You should be able to create your one-to-many join using the relations functionality. If you go to Project > Project Properties > Relations you can set up the relationship between the two layers in here and I believe one-to-many are supported:

 

 

Cheers,

Barrett

 

 

 

Barrett Higman

Alpine Shire Council

This transmission is confidential. This email, including any attachments, is for the original addressees only. Any use, copying or disclosure by any other person is prohibited. If you have received this transmission in error, please delete this message and notify us by email immediately at in...@alpineshire.vic.gov.au 

--
You received this message because you are subscribed to the Google Groups "Australian and New Zealand QGIS User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-use...@googlegroups.com.
To post to this group, send email to australian-qg...@googlegroups.com.
Visit this group at http://groups.google.com/group/australian-qgis-user-group.
To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/2078e901-0e94-4eb7-a6aa-e858b1867281%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ernie Johnson]

Hi Barrett,

 

Thanks for the reply.  I am able to set up a relation, but it really doesn't help for what I want to do.  Relations are good for queries on individual features, but I can't do any analysis on it.  I can't search on it, export it, or even look at a table. 

 

Regards,

Ernie

 

On Thursday, 5 February 2015 11:02:57 UTC+8, Barrett Higman wrote:

Hi Ernie,

 

You should be able to create your one-to-many join using the relations functionality. If you go to Project > Project Properties > Relations you can set up the relationship between the two layers in here and I believe one-to-many are supported:

 

 

Cheers,

Barrett

 

 

 

Barrett Higman

Alpine Shire Council

This transmission is confidential. This email, including any attachments, is for the original addressees only. Any use, copying or disclosure by any other person is prohibited. If you have received this transmission in error, please delete this message and notify us by email immediately at in...@alpineshire.vic.gov.au 

 

From: Ernie Johnson [mailto:ernest....@busselton.wa.gov.au]
Sent: Wednesday, 4 February 2015 3:17 PM
To: australian-qg...@googlegroups.com
Subject: [Aus-NZ-QGIS-group] Join - One to many

 

Hello Everyone,

 

I am having a problem joining a text file to my vector cadastre lot layer and was hoping someone could help.  We are identifying lots that have multiple zones assigned to them.  Usually, they are overlaps where the cadastre layer and the zoning layer don't match up exactly. 

 

We have our cadastre layer with all the lots in it, and we have a text file that lists all the lots that have multiple zoning associated with it.  All of our cadastre lots have a unique PIN number assigned to them.  The text file also has these PIN numbers, and that is what I'm using for the join.  However, the text file has multiple records for some of the lots (where there is more than just one overlapping zone) and we need to keep those records separate.  So, essentially I need to do a one to many join.  When the join is performed, only the first unique record from the text file is joined to the vector layer, so I am ending up with many lost records.

 

I can set up a relation, however this only works for individual searches.  Also, the relation is saved in the project and not in the attribute table or a new shapefile, so I can't do analysis with it.

 

I tried reversing the join (joining the vector layer attribute table to the text file).  That keeps all the records, but now there is no spatial component to the data.

 

Has anyone had to do a many to one join before in QGIS?  There are ways to do it in ArcMap and MapInfo, but I would rather stick with QGIS.

 

Regards,

Ernie

--
You received this message because you are subscribed to the Google Groups "Australian and New Zealand QGIS User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-user-group+unsub...@googlegroups.com.
To post to this group, send email to australian-q...@googlegroups.com.

[Ernie Johnson]

Hi Tom,

 

There were a number of workarounds that we could use, it's just that none of them worked natively in QGIS with Shapefiles.  It would work with TAB files, MSSQL, and geodatabases, but all those require exporting the data into different formats, using a different program to perform the join, and then bring it back into QGIS. 

 

In the end, we just did it in MapInfo.  We converted the Shapefile into a TAB file, joined the text file to it, and then exported the results back into a shapefile.  Doing it that way worked fine and it took just a matter of minutes.  I just wish there was an easier way to do it in QGIS as I'm trying to use it exclusively.

 

Thanks,

 

Ernie