specify: location

[MortenHofft]

ERROR: missing data for column "accepted_georeference_id" CONTEXT: COPY location, line 2: "ca4b779b-2f6f-4573-9675-65a068103b50,,,"North America, United States, Oklahoma, Le Flore",North Amer..." SQL state: 22P04

[MortenHofft]

If I instead only import a subset of the columns

COPY public.location(parent_location_id, location_id, higher_geography_id, higher_geography, continent, water_body, island_group, island, country, country_code, state_province, county, municipality, locality, minimum_elevation_in_meters, maximum_elevation_in_meters, minimum_distance_above_surface_in_meters, maximum_distance_above_surface_in_meters, minimum_depth_in_meters, maximum_depth_in_meters, vertical_datum, location_according_to, location_remarks)
FROM '/Users/user/Documents/dev/git/gbif/model-material/specify/location/LOCATION.csv' 
DELIMITER ',' 
CSV HEADER;

I get this error

ERROR:  null value in column "location_id" of relation "location" violates not-null constraint
DETAIL:  Failing row contains (null, ca4b779b-2f6f-4573-9675-65a068103b50, null, North America, United States, Oklahoma, Le Flore, North America, null, null, null, United States, null, Oklahoma, Le Flore, null, Poteau River from Highway 9A to 3 miles downstream, null, null, null, null, null, null, null, null, null, null, null).
CONTEXT:  COPY location, line 2: "ca4b779b-2f6f-4573-9675-65a068103b50,,,"North America, United States, Oklahoma, Le Flore",North Amer..."
SQL state: 23502

[tucotuco]

@acbentley The file LOCATION.csv is missing the final two columns (accepted_georeference_id and accepted_geological_context_id).

@MortenHofft It looks like the error from the subset import attempt has the parent_location_id and location_id order switched.

[MortenHofft]

Ah, thanks @tucotuco - I'm getting tired and just assumed columns were ordered same as schema. I'll look again

[MortenHofft]

changing the ordering i get

ERROR:  value too long for type character(2)
CONTEXT:  COPY location, line 18, column country_code: "USNC"
SQL state: 22001

Didn't "we" fix this issue already? Perhaps another file and it wasn't updated everywhere

[tucotuco]

@acbentley This was supposedly fixed in https://github.com/gbif/model-material/issues/43. The LOCATION.csv file in the repository still has non-country codes in it.

[MortenHofft]

It was reintroduced in https://github.com/gbif/model-material/issues/48 I see

[acbentley]

My bad. Forgot to replicate the deletion step when I redid the file. The below should be fixed LOCATION.csv

[acbentley]

Missed @tucotuco comment about missing columns above. These are now added in the below LOCATION.csv

[MortenHofft]

new error

ERROR: duplicate key value violates unique constraint "location_pkey" DETAIL: Key (location_id)=(99c81b6e-1781-4114-86ec-d4cdb669e415) already exists. CONTEXT: COPY location, line 90 SQL state: 23505

[acbentley]

This is because my depth fields (that are included in location here) are in the collecting event attribute table in Specify. This is a locality query so whenever there are multiple events per locality it will duplicate the GUID for the locality. This is to allow for localities (particularly non-specific localities) to be reused for multiple collecting events but for depths (and elevations) to differ. I think for now I will just have to delete duplicates in the file and hope that it doesn't mess up the event file. There were probably 20-30 of them. They are removed in the below

LOCATION.csv

[MortenHofft]

it now imports without errors 🎉

[MortenHofft]

this line 8730 looks wrong

\,,,"Pacific Ocean, Tonga, Tongatapu",Pacific Ocean,,,,Tonga,,Tongatapu,,,Mui Hopo Hoponga at east end of Island,,,,,,,,,,,

[acbentley]

Not sure what happened there. Corrected below

LOCATION.csv