Need SQL command to add centroids layer to the spatial service

[adam-collins]

A lot of the discussion around this is in this slack thread here: https://atlaslivingaustralia.slack.com/archives/CCSPJEU00/p1729809653636619

TL;DR: I created a centroids layer and am trying to add it to layersdb using these commands:

sudo -u postgres psql layersdb
layersdb=# SELECT * from fields where id = 'cl11221';

However, I'm not able to see the layer after running the following command:

layersdb=# select count(*) from objects where fid = 'cl11221';

the layer is not in the table. Can someone help me find the correct SQL command so I can add this layer and document it in Confluence?

[adam-collins]

Loading a points only spatial layer into spatial-service has only taken place 2 or 3 times. Due to this frequency, the admin UI was not intended to assist. In practice, more technical steps are required to load points only spatial layers. Testing is also different. Moving a tested layer into production may also require the same technical steps.

The points layers currently in the spatial-service is the Gazetteer of Australia.

• It is not intended to be added as a mappable layer in the spatial portal.
• It is intended for searching to find coordinates of a location.
• It is intended for use with the spatial portal's "nearest locality" tool.
• It is not intended for intersecting with coordinates.

Testing is performed using 3 services. For example, the gazetteer layer cl2123 can be tested with:

• https://spatial.ala.org.au/ws/field/cl2123?pageSize=10. This will list the field information and 10 objects with information including; pid, name, featureType, centroid, fid, fieldname, description, wmsurl, area_km, bbox.
• https://spatial.ala.org.au/ws/search?q=cape%20moreton&include=cl2123. This will do a text search on the field cl2123, suitable for an autocomplete, and the response includes; name, description, pid, fieldname, fid.
• https://spatial.ala.org.au/ws/object/8688403. For a given pid, this service returns information about a specific object.

Additional steps are required to load a points layer. Using the incomplete load of "Suburbs and Localities Centroids 2021" points layer in the test environment as a starting point, the tests show that the objects are not loaded. These additional steps are required:

• Disable the layer. This can be done through the UI.
• In the field, ensure the following fields have these values: enabled=true, indb=false, addtomap=false, namesearch=true, intersect=false, analysis=false, defaultlayer=true, layerbranch=false
• Construct a list of Postgresql SQL that will insert each new record into the layersdb.objects table.

The insert statement construction

• See spatial-service code for pid, the_geom and bbox

  spatial-service/grails-app/services/au/org/ala/spatial/SpatialObjectsService.groovy

  Line 764 in a67c8dd

  String sql = "INSERT INTO objects (pid, name, \"desc\", fid, the_geom, namesearch, bbox, area_km) " +

• area_km = 0
• name = name of the point
• description = description of the point, e.g. state, ending with space delimited latitude and longitude (see above test examples for cl2123)
• fid = ID of the field created earlier
• id = do not insert, it has a default from a sequence
• namesearch = true, so that it can be used in the search service

Example

INSERT INTO objects (pid,  name, "desc", fid, the_geom, namesearch, bbox, area_km) values (nextval('objects_id_seq'::regclass), 'Renard Point', 'TAS -40.58142 144.77414', 'cl11221', ST_GeomFromText('POINT(144.77414 -40.58142)', 4326), true, ST_AsText(Box2D(ST_GeomFromText('POINT(144.77414 -40.58142)', 4326))), 0);

Where

• Renard Point is the name. The will be unique for the entire file. For the given example "Suburbs and Localities" I expect that some suburbs or localities names will appear in multiple states, so these need to have an intutitive identifier appended to the name, e.g. Renard Point (Tas). Must not contain the character ' .
• TAS -40.58142 14477414 is the desc (description). This must end with the latitude followed by the longitude and delimited with a space. This is a mandatory requirement. Must not contain the character ' .
• cl11221 is the field id, previously created.
• POINT(144.77414 -40.58142) is the point as WKT. This appears twice as it is used for both the the_geom and bbox fields.

After constructing the list of INSERT statements, run this in the test environment postgresql layersdb and confirm.

[acbuyan]

This may be a dumb question, but am I to understand that I need to add each centroid individually via an INSERT command like the one shown above? And each one has to have a unique name?

[adam-collins]

That is correct. One centroid, one insert statement.

When there are non-unique names, they are made unique by adding a meaningful context such as type and state.

[acbuyan]

@adam-collins to confirm, is this an example of a correct statement? I ask because the example above does not have a bounding box:

INSERT INTO objects (pid,  name, "desc", fid, the_geom, namesearch, bbox, area_km) values (nextval('objects_id_seq'::regclass), 'Abercrombie River Centroid', 'Abercrombie River Centroid 149.34766214207687 -33.91032910627233', 'cl11221',149.34766214207687 -33.91032910627233,0

[adam-collins]

A comparison:

1. Change the name value by removing the word "Centroid" as this information will be in the layer metadata and applies to all records uploaded. I just now realise my example was "Renard Point" but this from the gazetteer and is the actual name not the shape.
2. Change the name value to denote the type of centroid after a comma. Be it a national park, town, etc. This is consistent with existing data.
3. Change the description value such that it does not repeat the name. If unsure what to use as a description prefix, use the state to be consistent with existing data.
4. Change the the_geom value to match given example. ST_GeomFromText...
5. Change the namesearch value to match the given example. true
6. Add the bbox value to match the given example. ST_AsText...
7. Add the area_km value to match the given example. 0
8. Ensure brackets are correctly closed.
9. Add a semicolon at the end of the statement.

[acbuyan]

Thanks Adam! I have now produced the script, which is in /home/buy003 on spatial-test, and have run it. I am running into some errors, because some of the objects are not adding because there's an apostrophe in the name of the suburb/locality, and I'm getting this error:

psql:SAL_centroids.sql:15296: ERROR:  syntax error at or near "Malley"
LINE 1: ...) values (nextval('objects_id_seq'::regclass), 'O'Malley, Ce...

Hoave you gotten this?

[acbuyan]

all right, I've fixed it and I have inserted all the objects into the database

[adam-collins]

I ran through the testing steps listed above

• https://spatial-test.ala.org.au/ws/field/cl11221?pageSize=10 reports 45977 objects for that field. The test object is also present. Description field needs work.
• https://spatial-test.ala.org.au/ws/search?q=O%27Malley&include=cl11221 returns one result. Description field needs work.
• https://spatial-test.ala.org.au/ws/search?q=Aarons%20Pass&include=cl11221 returns 5 identical results.
• https://spatial-test.ala.org.au/ws/object/21722230 appears correct.

I inspected the sql file.

• I assume that the use of "Centroid" is non-negotiable and this is why it was not removed from the name.
• Duplicating the name in the description is non-negotiable for me so please remove that part of the description.
• The sql file has 15334 lines and I assume this is the expected number of objects inserted. Running select count(*) from objects where fid = 'cl11221'; returns 45977 objects. I assume this is because there were partial runs before it worked. To resolve this, delete the prior inserts (for this fid only) and run again. To do this use delete from objects where fid = 'cl11221';

[acbuyan]

Hi Adam, I'm not sure exactly what you would like changed in the descriptions, could you please provide specific details or a description template for both the overall layer and the description in the SQL file? I would rather not make guesses in order to rectify any issues efficiently.