Given a polygon (Well Known Text or Well Known Binary), I want to enumerate the points. Normally, I would assign the polygon to a session variable and build an explicit loop from 1 to n.
SQLite supports neither session variables nor explicit loops which leaves me with one option - recursive Common Table Expression.
My code, shown below, does the job but I am not comfortable with the repeated embedded SELECTs to refer to the polygon (I am sure it impacts on performance adversely).
Is there a better way to do this?
The code:
DROP TABLE
IF EXISTS tmp;
CREATE TEMP TABLE tmp (Place text, shape blob);
INSERT INTO tmp (place,shape)
SELECT 'New York',GeomFromText('POLYGON((-79.78 42.27,-79.78 42.525,-79.77 42.535,-79.05 42.805,-78.95 42.845,-78.945 42.855,-78.925 42.905,-78.925 42.925,-79.025 42.965,-79.045 42.995,-79.045 43.03,-79.025 43.05,-79.085 43.06,-79.095 43.07,-79.095 43.09,-79.085 43.1,-79.09 43.13,-79.065 43.145,-79.075 43.17,-79.075 43.245,-79.085 43.245,-79.22 43.445,-79.22 43.46,-79.21 43.47,-78.695 43.65,-76.805 43.65,-76.455 44.105,-76.365 44.15,-76.32 44.22,-76.25 44.225,-76.2 44.24,-76.185 44.25,-76.18 44.29,-76.17 44.3,-76.14 44.315,-76.115 44.315,-76.005 44.37,-75.96 44.365,-75.94 44.375,-75.935 44.385,-75.84 44.445,-75.825 44.48,-75.78 44.53,-75.515 44.725,-75.44 44.77,-75.425 44.79,-75.385 44.8,-75.36 44.825,-75.325 44.835,-75.325 44.85,-75.22 44.9,-75.155 44.915,-75.15 44.93,-75.14 44.935,-75.035 44.965,-75.005 44.995,-74.98 45.005,-74.915 45.005,-74.895 45.02,-74.87 45.02,-74.845 45.035,-74.795 45.035,-74.785 45.025,-74.745 45.02,-74.745 45.01,-74.705 45.025,-74.335 45.01,-74.125 45.01,-73.815 45.025,-73.335 45.03,-73.325 45.02,-73.33 44.985,-73.32 44.975,-73.32 44.91,-73.33 44.895,-73.345 44.89,-73.345 44.87,-73.36 44.845,-73.35 44.845,-73.32 44.82,-73.315 44.775,-73.33 44.765,-73.345 44.735,-73.34 44.69,-73.365 44.615,-73.355 44.605,-73.355 44.585,-73.32 44.56,-73.32 44.55,-73.285 44.505,-73.275 44.42,-73.295 44.38,-73.315 44.36,-73.305 44.345,-73.305 44.305,-73.29 44.28,-73.3 44.24,-73.37 44.18,-73.38 44.145,-73.395 44.125,-73.395 44.095,-73.415 44.055,-73.385 44.025,-73.39 43.935,-73.355 43.885,-73.36 43.86,-73.35 43.845,-73.365 43.82,-73.34 43.8,-73.33 43.78,-73.33 43.76,-73.35 43.74,-73.355 43.71,-73.375 43.695,-73.4 43.645,-73.4 43.61,-73.38 43.645,-73.3 43.65,-73.285 43.64,-73.27 43.595,-73.23 43.565,-73.22 43.535,-73.23 43.495,-73.255 42.865,-73.27 42.8,-73.26 42.765,-73.245 42.755,-73.245 42.74,-73.485 42.085,-73.465 42.045,-73.53 41.3,-73.465 41.225,-73.465 41.205,-73.47 41.195,-73.5 41.18,-73.58 41.15,-73.695 41.09,-73.635 41.02,-73.64 40.995,-73.61 40.97,-72.09 41.28,-72.025 41.31,-71.925 41.33,-71.9 41.325,-71.78 41.195,-71.775 41.055,-71.79 41.025,-71.83 40.99,-72.32 40.82,-73.04 40.595,-73.245 40.55,-73.355 40.555,-73.53 40.515,-73.545 40.505,-73.6 40.505,-73.62 40.515,-73.635 40.51,-73.8 40.515,-73.845 40.495,-73.88 40.49,-73.89 40.475,-73.91 40.475,-73.98 40.5,-74.23 40.455,-74.27 40.475,-74.28 40.49,-74.28 40.51,-74.27 40.525,-74.27 40.555,-74.225 40.585,-74.22 40.645,-74.195 40.665,-74.145 40.66,-74.115 40.67,-74.065 40.67,-74.065 40.7,-74.045 40.72,-74.03 40.77,-73.955 40.88,-73.915 40.98,-74.165 41.095,-74.305 41.15,-74.325 41.165,-74.34 41.165,-74.45 41.225,-74.71 41.34,-74.715 41.36,-74.745 41.38,-74.76 41.395,-74.76 41.405,-74.795 41.4,-74.81 41.415,-74.84 41.41,-74.86 41.425,-74.905 41.42,-74.93 41.46,-74.955 41.455,-74.99 41.46,-75.005 41.475,-75.005 41.49,-75.02 41.495,-75.025 41.515,-75.045 41.525,-75.045 41.55,-75.095 41.595,-75.095 41.615,-75.07 41.635,-75.07 41.655,-75.08 41.665,-75.075 41.685,-75.085 41.69,-75.09 41.705,-75.085 41.725,-75.075 41.73,-75.075 41.75,-75.12 41.755,-75.125 41.785,-75.115 41.805,-75.125 41.805,-75.145 41.83,-75.17 41.83,-75.195 41.845,-75.225 41.835,-75.245 41.845,-75.27 41.845,-75.295 41.89,-75.295 41.925,-75.32 41.93,-75.335 41.95,-75.355 41.955,-75.36 41.98,-78.855 41.975,-79.775 41.98,-79.78 42.27))', 4326);
.mode column
.headers on
WITH RECURSIVE points (
n,
Longitude,
Latitude
)
AS (
VALUES (
1,
(
SELECT X(PointN(exteriorring(shape), 1))
FROM tmp
),
(
SELECT Y(PointN(exteriorring(shape), 1))
FROM tmp
)
)
UNION ALL
SELECT n + 1,
(
SELECT X(PointN(exteriorring(shape), n + 1))
FROM tmp
),
(
SELECT Y(PointN(exteriorring(shape), n + 1))
FROM tmp
)
FROM points
WHERE n < (
SELECT NumPoints(exteriorring(shape))
FROM tmp
)
)
SELECT *
FROM points;