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.

[adam-collins]

Use description template: STATE LATITUDE LONGITUDE

For an example see the above comment

[acbuyan]

Hi Adam, this has now been done.

[adam-collins]

The test URL https://spatial-test.ala.org.au/ws/search?q=Aarons%20Pass&include=cl11221 has the description "description": "New South Wales 149.8028857651389 -32.879094473951405",.

Update to match the requirement stated Use description template: STATE LATITUDE LONGITUDE

[acbuyan]

The description does contain the state though - do you mean only have the state abbreviation, rather than the full name of the state?

[adam-collins]

LATATUDE and LONGITUDE are reversed.

[acbuyan]

I've changed the positioning of lat/long and redid the objects

[adam-collins]

The test URL https://spatial-test.ala.org.au/ws/search?q=Aarons%20Pass&include=cl11221 has the name "name": "Centroid of Aarons Pass Suburb/Locality"

1. Remove the name prefix Centroid of.
2. Use the appropriate descriptor, not both Suburb and Locality.

[acbuyan]

I've removed the extraneous text

[adam-collins]

The test URL https://spatial-test.ala.org.au/ws/field/cl11221?pageSize=10 returns a list of objects with the incorrect centroid value. e.g. "centroid": "POINT(-32.879094473951405 149.8028857651389)", should be "centroid": "POINT(149.8028857651389 -32.879094473951405 )",. For an example INSERT statement refer to the one provided earlier.

[acbuyan]

I've swapped the lat/longs again and uploaded them to test and production

[acbuyan]

Hi @adam-collins @kylie-m @TaniaGLaity

I had a meeting with Kylie today to discuss this ticket. I have a few clarification questions:

• For the BBOX, I believe I have the BBOX formatted correctly - the latest statement I have is here, which looks like the correct format from the example you provided Adam:

INSERT INTO objects (pid,  name, "desc", fid, the_geom, namesearch, bbox, area_km) values (nextval('objects_id_seq'::regclass), 'Abercrombie River', 'New South Wales 149.34766214207687 -33.91032910627233', 'cl11221', ST_GeomFromText('POINT(149.34766214207687 -33.91032910627233)',4326), true, ST_AsText(Box2D(ST_GeomFromText('POINT(149.34766214207687 -33.91032910627233)',4326))), 0);

Am I missing something?

• In the description, does the state need to be abbreviated? If so, why? Is there a downstream program that needs the description like this?

[kylie-m]

Thanks @acbuyan we'll chat in standup and I can let you know!

[kylie-m]

Hi @acbuyan, I just checked with Adam and what you had above is very close, just needs to have the lat and lon swapped for desc, so for example:

INSERT INTO objects (pid, name, "desc", fid, the_geom, namesearch, bbox, area_km) values (nextval('objects_id_seq'::regclass), 'Abercrombie River', 'New South Wales -33.91032910627233 149.34766214207687', 'cl11221', ST_GeomFromText('POINT(149.34766214207687 -33.91032910627233)',4326), true, ST_AsText(Box2D(ST_GeomFromText('POINT(149.34766214207687 -33.91032910627233)',4326))), 0);

We also discussed having the state/territory written out in full vs acronym and Adam confirmed there is no back-end reason for that

[kylie-m]

Morning @acbuyan, I ran through the testing steps based on everything above and as far as I'm aware, it looks good to me.

Testing notes:

1. Lists field information and 10 objects with info - https://spatial-test.ala.org.au/ws/field/cl11221?pageSize=10

• the ordering of lat and lon appear to be correct in all cases - bbox and centroid (long lat), and desc field has state then lat lon
• also contains the expected number of objects, 15334


2. Text search on the field cl11221, with response including name, desc, pid, filename, fid - https://spatial-test.ala.org.au/ws/search?q=Aarons%20Pass&include=cl11221
   and https://spatial-test.ala.org.au/ws/search?q=O%27Malley&include=cl11221

• no duplicates, just 1 result for each
• description fields look correct
• name does not contain 'centroid of'

3. Return info on a given pid - https://spatial-test.ala.org.au/ws/object/21875656 and https://spatial-test.ala.org.au/ws/object/21906246

• responses match the corresponding fields in step 2 above, which is where the pids were taken from.

****** All as above appears correct on production too, the prod pids for the above are:
https://spatial.ala.org.au/ws/object/9542562
https://spatial.ala.org.au/ws/object/9573152

• I note that for these two links the order of the fields changes, not sure if that's of concern?
  name, wmsurl, description, pid, area_km, fid, fieldname, bbox, id versus
  name, wmsurl, pid, description, bbox, area_km, fid, fieldname, id,

I then started on next steps for testing the batch output check:

1. Intersect batch output
   https://sampling.ala.org.au/sampling-service/intersect/batch?fids=cl11221&points=-22,131,-25,145
   results:
   {"waiting":"In queue","statusUrl":"https://sampling.ala.org.au/sampling-service/intersect/batch/1742423990569","progress":0,"batchId":"1742423990569","fields":1,"points":2,"status":"waiting"}

2. status URL - shows 'finished'
   {"downloadUrl":"https://sampling.ala.org.au/sampling-service/intersect/batch/download/1742423990569","progress":2,"started":"20/03/25 09:39:50:570","finished":"20/03/25 09:39:50:573","fields":1,"points":2,"status":"finished"}

3. From the csv download (using the above download URL)
   returned:

latitude	longitude	cl11221
-22	131
-25	145

this output matches the output of another prod layer used as an example above, cl1213 -
(can compare using https://sampling.ala.org.au/sampling-service/intersect/batch?fids=cl2123&points=-22,131,-25,145, get status URL, download URL, check csv)

Summary

• Based on what I know, looks good to me
• Need to check - does it matter if the order of the fields differ?
• I've assumed the intersect batch output for cl11221 is correct based on comparison to existing cl1213 - but is it correct?
• Need to confirm - from here, I believe the next step is SOLR index update with new sampling (data team)
• Once confirmed, create / update testing docs on Confluence

[kylie-m]

Hi again @acbuyan, I ran the above past Adam, answers to questions in the summary:

• Looks good
• Order of the fields does not matter (it is a map data structure)
• Batch intersections do not apply to point layers (intersections are only suitable for areas, e.g. polygons and rasters)
• Does not apply to SOLR (no intersections)

I also needed to check using spatial.test not spatial-test, so I did the same tests above but using the equivalent links. Everything looked good as above, with one exception being that the description field had order lon lat rather than lat lon

[TaniaGLaity]

Thanks all for persisting with this, much appreciated!

[kylie-m]

I also should have said, once we fix the order of lat and long in description on spatial.test there should be no other steps left to do. Thanks @acbuyan ! :)