Fix a SQL Statement

$9.09 (inc. GST Australia)

Categories: ,

Description


Are you having trouble creating a SQL statement to do something important, or is it not running fast enough?

Send me the statement and I will have a go at fixing it.

You have to contact me via email (see contact form).

When you send me the SQL statement, if it is a SELECT statement don’t forget to send me the data too.

You can do this via a Common Table Expression (CTE) as follows:


WITH data AS (
/* Oracle ....*/
SELECT SDO_GEOMETRY('LINESTRING(0 0,1 1)',28355) as ora_geom FROM DUAL UNION ALL .....
/* SQL Server */
SELECT GEOMETRY::STGeomFromText('LINESTRING(0 0,1 1)',28355) as ss_geom FROM DUAL UNION ALL .....
/* PostGIS */
SELECT ST_GeomFromText('LINESTRING(0 0,1 1)',28355) as pg_geom FROM DUAL UNION ALL .....
)
SELECT columns.....
FROM data a;

For example:

With river_line as (
select 1 as gid, ST_GeomFromText('LINESTRING (9.091 64.229, 12.451 66.798, 17.391 68.379, 21.146 67.589, 29.842 67.589, 33.794 70.356, 38.538 71.542, 45.059 72.332, 52.372 71.739, 57.905 69.368, 59.684 67.194, 65.217 65.217, 73.32 65.217, 78.854 60.079, 79.842 54.941, 81.028 44.862, 85.178 39.921, 85.375 30.83, 82.609 24.901, 79.842 18.775)') as geom
), river_area as (
select 1 as gid, ST_GeomFromText('POLYGON ((8.003 66.926, 11.164 70.086, 13.692 70.929, 19.171 70.929, 23.385 70.508, 26.546 70.297, 33.078 71.983, 36.871 74.301, 43.824 75.776, 51.199 75.986, 59.206 74.511, 62.788 71.772, 64.685 70.719, 73.535 71.351, 78.592 69.244, 83.649 64.187, 84.913 62.501, 86.178 57.022, 85.756 53.019, 85.124 49.226, 86.81 45.433, 87.863 40.376, 89.338 37.215, 89.338 32.58, 87.653 27.522, 83.438 18.462, 81.12 15.933, 74.799 17.619, 77.538 25.205, 80.067 30.472, 80.488 37.215, 78.381 41.219, 75.22 53.229, 72.06 60.394, 62.999 63.133, 52.463 65.451, 46.353 66.926, 37.714 63.344, 29.496 62.501, 20.646 61.447, 14.114 62.922, 9.899 61.447, 3.157 63.765, 3.367 64.187, 8.003 66.926))') as geom
), floodplain as (
select 1 as gid, 'A' as classification, ST_GeomFromText('POLYGON ((-11.055 8.486, -9.294 47.979, -1.999 81.184, 30.451 92.755, 86.546 85.208, 115.474 69.109, 115.726 27.352, 119.751 4.964, 45.795 11.001, -0.742 6.222, -11.055 8.486))') as geom
)
select ST_Length(rl.geom) as river_length,
ST_Area(fp.geom) as floodplain_area,
ST_area(ra.geom) as river_area,
ST_Area(ST_Intersection(ra.geom,fp.geom)) as iArea
from river_line as rl,
river_area as ra,
floodplain as fp;

Reviews

There are no reviews yet.

Be the first to review “Fix a SQL Statement”

Your email address will not be published. Required fields are marked *