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