Hi all,
my database has a table ExifPhoto with the fields GPSGeometry and Nearby.
For each row I need to Update field Nearby with all coordinates (in the
table) within a Distance of 1 degree of the coordinates in field
GPSGeometry.
The sqllite database has the spatial extension from SpatiaLite enabled.
So far I have this sql query, which puts all POINTs from all GPSGeometry
fields into field Nearby for each row.
Ideas on how to limit POINTS to coordinates within a Distance 1 degree
please?
Thanks,
Jan
See the DISTANCE sql for how to do a spatial distance query.
I guess one needs to use PointFromWKB instead GeomFromTex because there is a
POINT already in the field GPSGeometry. But how to use a db field instead of
a hard-coded point?
Puts all Points from field GPSGeometry into field Nearby:
UPDATE ExifPhoto SET Nearby = (
SELECT
GUnion(exif1.GPSGeometry) AS GUnion1
FROM
ExifPhoto AS exif1
INNER JOIN
ExifPhoto AS exif2
ON
exif1.GPSGeometry = exif2.GPSGeometry
);
Working example of Distance search:
SELECT GUnion(GpsGeometry)
FROM ExifPhoto
WHERE Distance(GpsGeometry, GeomFromText('POINT(11.0 43.0)', 4326)) < 1;
SpatiaLite-GUI -> BLOB explorer -> Geometry Explorer:
SRID: 4326
Geometry type: MULTIPOINT
#6 POINTs:
1) 11.8791 43.4660
2) 11.8792 43.4673
3) 11.8802 43.4682
4) 11.8815 43.4645
5) 11.8815 43.4684
6) 11.8816 43.4684
So 6 coordinates are within 1 degree of the coordinates 11.0 43.0.
There are 9 rows alltogether in the database for testing.
Spatialite: http://www.gaia-gis.it/spatialite/
exif example: http://www.gaia-gis.it/spatialite/spatialite-exif-2.3.0.html
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
