4

I've imported data from S-57 format into a PostgreSQL/PostGIS database using ogr2ogr (GDAL 2.1.0) and it resulted in several tables; one for each feature type. I intend to merge these tables into a single table.

However, the current primary keys for the tables are ones which I think was introduced by the importer, ogc_fid, as the values count up from 1 with increments of 1 for each table. That means that the values would not be unique after a merge and can thus not be used as primary key. I intend to drop that column after or before the merge as it doesn't fit my needs.

That leaves three columns which are candidates for being used as primary key, and I'm here to ask which one to pick as I'm not very familiar with the S-57 format.

RCID: This column is not listed here. I've not found information on this column in many places (Googling didn't return many relevant results), though one place referred to it as something like "Record Identification". From what I can see, it contains the same value as FIDN, though I can't say for sure that so is the case for all features.

FIDN: Feature Identification Number. It stands to reason that this one would be unique for each feature, but I'm not so sure. Is it possible that a feature could fall under multiple feature types and thus the same FIDN show up in multiple tables? I'm not familiar enough with S-57 to know.

LNAM: Long Name. It's a combination of AGEN, FIDN and FIDS. So far all features I've seen have had the same values in AGEN and FIDS, so I don't see how this one would be more unique than FIDN if the same FIDN value could appear in multiple tables.

If I stay in doubt I'm probably going to make some combination of FIDN and OBJL into the primary key, because every table have different OBJL values (because it identifies the feature type) and the FIDN should at least be unique within each table.

EDIT: Upon closer inspection I see that the OBJL is already included in the FIDN. So they are already unique between tables.

  • 1
    if you start fiddling around with the data why not create an entirely new column as primary key? – LaughU Jul 13 '17 at 13:23
  • Yes, that is an option. I could make my own version of the ogc_fid column. That solution didn't really occur to me. This question had the secondary purpose of learning a bit more about the S-57 format though. :P If anyone knows which columns I can trust to be entirely unique across tables I'd love to hear it. – Kapten-N Jul 13 '17 at 13:41
  • 1
    I can only hint you to a solution for the primary key issue but I dont know the exact nature of the S-57 format. Maybe drop a mail to a developer and try your luck with them :) – LaughU Jul 13 '17 at 13:53
  • Okay. Thank you. Your answer below seems helpful. I've never studied SQL and I'm learning as I work, so every bit helps. :) – Kapten-N Jul 13 '17 at 13:56
5

if you are not sure if a column is a candidate for a primary key. You could run a series of this commands:

  • check the total number of rows in you final table

    select count(*) from   my_table;
    
  • check the distinct numbers for each candidate column with

    SELECT  DISTINCT on(RCID) * FROM my_table;
    SELECT  DISTINCT on(FIDN) * FROM my_table;
    SELECT  DISTINCT on(LNAM) * FROM my_table;
    
  • if the numbers differ from the total row count, this column is not a proper primary key because of duplications.

  • If you can choose you can create a new column such as:

    ALTER TABLE my_table ADD  COLUMN my_id serial PRIMARY KEY;
    
| improve this answer | |
  • Using this method I've found that, at least in my data, FIDN and RCID are indeed the same, so I'm just going to drop the RCID column. I've also found that the values in FIDN often reoccur in different datasets making it a poor choice for a primary key, since I intend to append several datasets into the same table. The different datasets have different values in the FIDS column though, which makes the LNAM unique and thus the best choice for primary key. Thanks again for your help. – Kapten-N Jul 18 '17 at 13:33
  • Actually, LNAM might not be suitable as a primary key since it's a string... It will work, but there will be a larger overhead since a string comparison is more expensive than a numerical comparison. – Kapten-N Jul 18 '17 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.