Updating records

[colinx...@gmail.com]

Apologies for diving straight into this group with a query but I've been scratching my head for a while to come up with a reasonably efficient method.

What I have is a Spatialite database in which I store a large number ( >200,000,000) of geometry points, each of which represent an altitude measurement and I also store a series of areas as polygons which
represent a grid which I can use to abstract areas for raster generation. My problem is that some of these areas are "perfectly formed" and represent high quality lidar data while others are "patchy"
and require further data. I acquire this extra data in the form of gps tracks which cover the entire area. Now as this new data is in of poorer quality but none-the-less still valuable to the overall picture,
I would like to ignore any new datapoints that are in areas of known "good" data when importing these tracks.

Now clearly if I was SELECTing data, I think I could form a union of all the "good" areas and use a NOT IN clause but how do I do this for an INSERT statement?

The only schemes I come up with so far involve creating temporary views and the selecting a subset of the view for inserting. Bearing in mind I sometimes have several dozen tracks at a time
to deal with, I'd like it to be reasonably efficient.

Anyone any ideas or should I consider writing a bit of code to pre-process the track files?


Colin B

[Goyo]

Hello Colin, i'm not sure I understand the issue but if you know how to do the SELECT part then you can just

INSERT INTO <target_table> SELECT <you_already_know_what>

So maybe I am missing something?

Now if your concern is the eficiency of the SELECT, I am not relly sure what you want or how your tables actually look. I'm nor very good at optimizing SQL anyways.

Goyo

[colinx...@gmail.com]

Many thanks for your thoughts - the "new" data is in an obscure format and I was processing 1 record at a time - perhaps it would be better to dump all new data
onto a temporary table/view and then select from there.

Regards Colin B

[colinx...@gmail.com]

[mj10777]

After working through:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Ground+Control+Points 

I started building up a list of gcp points for geo-refering historical maps

- classifying the points based on the 1st,2nd,2rd order 

 -- 0=tpc (Thin Plate Spline) 

 -- 1=1st order(affine) [precise]; 

 -- 2=2nd order [less exact]; 

 -- 3=3rd order [poor]

--

So assuming you have a 'raster_area' table with an entry for each area, classified as

1=1st order [precise]

2=2nd order [good, but could be better]

3=3rd order [poor]

You could then create a 'raster_area_reference' table

- ONLY done once

- OR UPDATED when the classification of an area has changed

INSERT INTO raster_area_reference

(id_order,area_count,area_geometry)

(

1,

(SELECT count(area_geometry) FROM raster_area WHERE id_order=1),

(SELECT CastToMultiPolygon(ST_UnaryUnion(area_geometry)) FROM raster_area WHERE id_order=1)

);

INSERT INTO raster_area_reference

(id_order,area_count,area_geometry)

(

2,

(SELECT count(area_geometry) FROM raster_area WHERE id_order=2),

(SELECT CastToMultiPolygon(ST_UnaryUnion(area_geometry)) FROM raster_area WHERE id_order=2)

);

INSERT INTO raster_area_reference

(id_order,area_count,area_geometry)

(

3,

(SELECT count(area_geometry) FROM raster_area WHERE id_order=3),

(SELECT CastToMultiPolygon(ST_UnaryUnion(area_geometry)) FROM raster_area WHERE id_order=3)

);

Adding a record with:

0=outside of range

A query could then be developed (pseudo code)

SELECT id_order FROM raster_area_reference
WHERE
(

  ROWID IN 

 (

  SELECT ROWID FROM SpatialIndex

  WHERE 

  (

   (f_table_name = 'raster_area_reference') AND

   (f_geometry_column = 'area_geometry') AND

   (search_frame = MakePoint(ST_X(NEW.gps_point), ST_Y(NEW.gps_point)))

  )

 )

);

for a 'BEFORE INSERT' trigger (again pseudo code)

CREATE TRIGGER 

 "ggi_gps_points" BEFORE INSERT ON "gps_points"

FOR EACH ROW BEGIN

SELECT RAISE(ROLLBACK, 'gps_points: [out of range OR inside a 1st order Area]')

WHERE 

(

 -- above query

) IN (0,1);

END

which could prevent an INSERT, where not desired.

Mark

Colin B

[mj10777]

a more realistic pseudo query could be

SELECT id_order FROM raster_area_reference 

WHERE

(

 (

  ROWID IN 

  ( -- skip where POINT is outside the BoundingBox

   SELECT ROWID FROM SpatialIndex

   WHERE 

   (

    (f_table_name = 'raster_area_reference') AND

    (f_geometry_column = 'area_geometry') AND

    (search_frame = NEW.gps_point)

   )

  )

 ) AND

 -- ST_Contains only when inside the BoundingBox of the POLYGON

 (ST_Contains(area_geometry,NEW.gps_point) = 1)

);

[colinx...@gmail.com]

Many thanks again for the input - I shall study the replies but I have found a way of doing it although it would need someone more knowledgeable than me to comment on whether its a viable method

One of my problem relates to having to first decode a binary file to get the data so what I have done is as follows:

Created a temporary table ( tempdata) into which I insert all the records from the binary file (after checking for duplicate or corrupt records.)

I've also created a "done" field for the grids file ( contains grid polygons) ; 1 = grid area completed i.e.add no more records; 0 is add new records.

I've then created this select statement (to be used in the insert into main table)
SELECT altitude,geom FROM (SELECT ST_Union(grids.Geom) AS Allgrids FROM grids where grids.done >0), tempdata WHERE Disjoint(Allgrids, tempdata.Geom);

Surprisingly (for me) it seems to work although I will always accept improvement advice.

Regards Colin B