sql: How to populate MultiPoint?

[Jan Martin]

Hi all,

I have a spatialite database with lots of points.

1) I'd like to add a MultiPoint column named Nearby.
Is the fieldtype BLOB right?

Copied from Firefox SQL Manager:
ALTER TABLE "main"."mytable" ADD COLUMN "Nearby" BLOB

2) Then for each point in the table I'd like to populate the MultiPoint field with all the points within 50 meters of the point.

What might the sql look like?

Hint:
"Distance" needs degrees, so 50 meters = 0.00045 degrees:
0.05 km*(360 degrees / 40000 km earth circumference)

Thanks,
Jan

[a.fu...@lqt.it]

* I'd like to add a MultiPoint column named Nearby.

* Is the fieldtype BLOB right?
* ALTER TABLE "main"."mytable" ADD COLUMN "Nearby" BLOB

No, this is wrong. You cannot create a Geometry in such a simplistic way.
In order to create a 'true' Geometry you must use AddGeometryColumn(), as in:

SELECT AddGeometryColumn('some_table', 'Nearby', 4326, 'MULTIPOINT', 2);

Please note: 4326 means WGS-84 (I'm not really sure if you intend exactly this,
but you must explicitly select some SRID anyway).


* Then for each point in the table I'd like to populate the MultiPoint field with
* all the points within 50 meters of the point.
* What might the sql look like?
* Hint:
* "Distance" needs degrees, so 50 meters = 0.00045 degrees:
* 0.05 km*(360 degrees / 40000 km earth circumference)

I fear there isn't a reasonable simple and efficient way to perform such
a task using only some SQL statement.
It seems by far wisest writing some high-level language routine
(e.g. in Python) to 'soften' this task.

Anyway, you can easily use SQL in order to retrieve any point falling
within some distance range from an arbitrary 'center' at your choice,
and then build a MULTIPOINT.
Try using the following SQL snippet:
 
SELECT GUnion(geometry)
FROM some_table
WHERE Distance(geometry, GeomFromText('POINT(11.0 43.0)', 4326)) < 0.00045;

Bye,
Sandro

[Andrea P.]

Hi Martin,

I populate a spatialite db using some sql strings like these:

1) create a table like this:

sql="CREATE TABLE TAB1(field_A varchar(125), field_B varchar(125) )";
< execute this sql>

ps: even if spatialite is very like postgis, in qgis don't need to have
an oid field to use it (postgis need an oid field instead) , so is not
necessary to put an oid field on the spatialite table.

2)
add a geometry column:
with a code sql like this:

sql="SELECT AddGeometryColumn( 'public','tab1', 'geom',
4326,'LINESTRING', 2 )";
<execute this sql>

3)
to add data, you can use a sql insert like this:

sql="INSERT INTO TAB1(field_a,field_b,geom) VALUES
('AAA','BBB',GeomFromText('LINESTRING(9.01 39.0, 11.1 40.2)',4326));

Andrea.


Jan Martin ha scritto:

[NiceDogE]

Jan,

I'm afraid building and disaggregating MultiPoint objects is something
that probably can't be done by SQL alone (not in SQLite anyway where
you don't have iteration calls available). I've only done this by
combing SQLite with an external programming code (eg. Visual Basic)

Maybe you should ask, "To represent the nearest points, should I use a
MultiPoint Field?). I would say, "No!" They are hard to deal with.

Instead, represent the nearest points by
a) A 2nd table "NearestPoints", with fields "BasePointRowID Integer
Primary Key, NearestPointGeometry BLOB"

b) Better (more space and query efficient):......I assume the Nearest
Points is already being derived out of an existing table that contains
these Point Geometries. In this case, just create a new Table
"NearestPointsRowIDs" that has 2 fields, "BasePointTableRowID Integer
Primary Key, NearestPointTableRowID Integer". Then you can use this
"ID Match" as a joining table in any of your queries. Muuuuuuch
easier to use.

A small point, but aside from AddGeometryColumn(), there is a 2nd
method that does much the same thing:
a) Add a BLOB field
b) Use RecoverGeometryColumn() on the BLOB field

> > Jan- Hide quoted text -
>
> - Show quoted text -

[NiceDogE]

Oops, I just realised that in both case where I've put "Primary Key",
those fields can't be primary key fields, so delete that part of the
syntax. The rest of the sentences are OK!

> > - Show quoted text -- Hide quoted text -

[Jan Martin]

Hi all,
hi NiceDogE,

thank you for your insight.

My initial error was that I wanted points "nearby" and so  went for DISTANCE, when BUFFER would have been the better solution.

My working solution looks like this:
Inside a "for x in y loop" of the photoid(s) of all rows I do (in python):

Update ExifPhoto SET Nearby = (
SELECT GUnion(Intersection(Buffer(GeomFromText(:point, 4326), 0.005),GpsGeometry))
FROM ExifPhoto)
WHERE photoid = :photoid", {"point": point, "photoid": photoid});

Do you think this is a good solution?

Thanks,
Jan

[NiceDogE]

Well, I'll talk about DISTANCE vs BUFFER first. I'll leave the
Python script alone just at the moment.

I had a very similar problem to yours, and you should be able to use
Distance() no problem. In fact, used properly it will be far faster
than using computationally complex functions like Buffer() and GUnion
(). I can add further that I have ALSO used the Buffer() type
solution in the past to find nearest geometries. It works, but
performance-wise it isn't anywhere near as good as below.......waaaaay
slower, plus I needed a lot of code to analyse the Muliple Points
returned inside the Buffer() object.

Basically, your problem is "What Points are within x distance of this
Point?"

Here is how I solved my problem using SQL alone. (This is called the
"Nearest Neighbour" problem, if you search Google for this, you will
find several worked SQL examples, but not a lot for SpatiaLite).

In fact, Alessandro has provided a similar solution in this forum not
so long ago, and also has similar SQL script in his tutorial where
discussing Spatial Indices.

- I'll call the Table with the X,Y coords for the Reference Point
"RefPointsXY"
- I'll call the Points table you are searching for Nearest Points
"SearchPoints"

1. Create a Spatial Index on the Search Points Geometry field. Use
SpatiaLite function CreateSpatialIndex()

2.

a) SQL to return nearest points within say 50 distance units becomes:

SELECT S.Geometry 'NearestPointGeom'
FROM SearchPoints 'S', RefPointsXY 'R'
WHERE S.RowID IN(Select pkID FROM idx_SearchPoints_Geometry
WHERE xmin<X(MakePoint(R.X,R.Y))+50 AND xmax>X(MakePoint(R.X,R.Y))-50
AND
ymin<Y(MakePoint(R.X,R.Y))+50 AND ymax>X(MakePoint(R.X,R.Y))-50)
AND Distance(S.Geometry,MakePoint(R.X,R.Y)) < 50

For each Reference Point X,Y this yields multiple rows of Point
objects that are within 50 distance units of the Reference Point X,Y.

Obviously there are spots where you can parameterize this SQL
statement eg. The SearchDistance, The MakePoint(R.X,R.Y) statement
etc. and also get more performance out of it, but I've kept the
example syntax simple.

You can also introduce ORDER BY on the Distance() expression, and a
LIMIT clause to limit the returned points to say....the nearest 5
points maximum within 50 distance units.

b) I would personally write an SQL query that returned the RowID from
the "SearchPoints" table instead of returning the full Point
Geometry....but you may want to be storing separate copies of the
Nearest Points for some reason, in which case use example a) above.

So alternatively SQL becomes:
SELECT R.RowID 'RefPointRowID', S.RowID 'NearestPointRowID'
FROM SearchPoints 'S', RefPointsXY 'R'
WHERE S.RowID IN(Select pkID FROM idx_SearchPoints_Geometry
WHERE xmin<X(MakePoint(R.X,R.Y))+50 AND xmax>X(MakePoint(R.X,R.Y))-50
AND
ymin<Y(MakePoint(R.X,R.Y))+50 AND ymax>X(MakePoint(R.X,R.Y))-50)
AND Distance(S.Geometry,MakePoint(R.X,R.Y)) < 50

> > > - Show quoted text -- Hide quoted text -