SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Azure TSQL - to create Junction Table using Spatial query


Azure TSQL - to create Junction Table using Spatial query

Author
Message
Dalkeith
Dalkeith
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1299 Visits: 1236
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.




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search