I know this sounds easy and lazy to ask, but I'm new to (spatial)database, and I have the feeling my solution is sub-optimal. But for my situation it's important to have good performance.
What do I have now:
I reduced the size of the view, by making it from the MBR of subgroups of points (i.e. 1-50, 51-100, 101-150). This reduced the size of the view dramatically, and speeds up the whole process.
What do I want:
A general question I have is: Am I using the right approach to get plots in which the points are? Especially because I know the points are on a line and have a known distance between them.
And more specific: I believer my query is inefficient. Because I know each point is in only one plot, I would like the 'Within-function' to continue to evaluating the next point as soon a it finds the first point-plot match. This could reduce the number of test by 50%.
How do I enforce this? Putting a 'LIMIT 1' at the end of the query will only result in the answer for one point.
أكثر...
What do I have now:
- Python 3.4 @ Win7-64,
- SQLite v3.8.11.1 with Spatialite v4.3.0 via APSW
- Two tables; Plots (up to 1m rows) and Points (100 to 200 rows). In real life the points lie on a (almost) straight line.
- The position of the points in update constantly, and after each update I need to find out in which plot the points are.
- A View containing all plots inside the MBR of the points-table. The view is made by use of a SpatialIndex.
CREATE VIEW 'View' AS SELECT * FROM Plots, idx_Points_geom WHERE idx_Plots_geom.xmax > (SELECT MIN(xmin) FROM idx_Points_geom) AND idx_Plots_geom.xmin < (SELECT MAX(xmax) FROM idx_Points_geom) AND idx_Plots_geom.ymax > (SELECT MIN(ymin) FROM idx_Points_geom) AND idx_Plots_geom.ymin < (SELECT MAX(ymax) FROM idx_Points_geom) AND idx_Plots_geom.pkid == Plots.id - The following query, giving me for each point the corresponding plot.
SELECT * FROM "Points", "View" WHERE Within("Points"."geom", "view"."geom")
I reduced the size of the view, by making it from the MBR of subgroups of points (i.e. 1-50, 51-100, 101-150). This reduced the size of the view dramatically, and speeds up the whole process.
What do I want:
A general question I have is: Am I using the right approach to get plots in which the points are? Especially because I know the points are on a line and have a known distance between them.
And more specific: I believer my query is inefficient. Because I know each point is in only one plot, I would like the 'Within-function' to continue to evaluating the next point as soon a it finds the first point-plot match. This could reduce the number of test by 50%.
How do I enforce this? Putting a 'LIMIT 1' at the end of the query will only result in the answer for one point.
أكثر...