Azure TSQL - to create Junction Table using Spatial query

• Dalkeith

  Hall of Fame

  Points: 3687

  More actions

  October 20, 2017 at 6:07 am

  #324215

  Just trying to get a valid result set at present. 

  I have created a B basic single database 5DTU 2GB within an Azure instance on my account.

  I have downloaded a copy of all greenspaces in the UK  (available from the Ordnance Survey) and a copy of larger administrative boundaries - in this case Westminster Boundaries.
  I downloaded them in shape format and uploaded them using geometry field type for the coordinates to two tables in my Azure Database. I have linked to these tables in Azure and displayed them through QGIS. (an open source geographical information system just to check that the upload was successful and data made sense). Both of these datasets contain Polygon Records

  Data available here
  Ordnance Survey Open Data

  I am now looking to create a junction table that uses some of the spatial functions available in SQL Azure to identify which Westminster boundary IDs the greenspaces are in.

  SELECT osref, polygon_id
FROM dbo.ukgreenspace a, dbo.westminster_const_region b
WHERE a.ogr_geometry.STWITHIN(b.ogr_geometry)=1;

  This query seems to execute however it runs for 14 minutes and then I get a nil result
  osref, polygon_id - I have also tried STContains same result.

  OS ref is a unique key from uk greenspace and polygonId is a unique key from a westminster boundary table. Everything is in geometry rather than geography field type.

  2 questions
  Is this SQL Correct?
  Given that there are some 141,579 records in the greenspace table and over 400 in the westminster boundaries is this likely to fail the query? (I note it maxes out the DTUs when executing the query)

  Its not an issue that it takes time to calculate just trying to get it to return the correct result set at present.

  I'm experimenting categorizing polygons in catchments (any type of catchments) to see after capture I can update and append a junction tables that could then be displayed in a masters details form.

  Any advice or guidance much appreciated. I know I could probably use QGIS to create the junction table but I want to fully automate through TSQL.