Styjun

Where can I find visible/radio telescopic observations of the center of the Milky Way galaxy?

Teacher help me explain this to my students

what kind of chord progession is this?

What are these arcade games in Ghostbusters 1984?

Is it true that cut time means "play twice as fast as written"?

A steel cutting sword?

Why would Ryanair allow me to book this journey through a third party, but not through their own website?

Website returning plaintext password

What is the object moving across the ceiling in this stock footage?

Can I tell a prospective employee that everyone in the team is leaving?

Plot and know intersection points of multiple lines/functions

Sitecore 9.0 works with solr 7.2.1?

How to deal with a colleague who is being aggressive?

Is the Indo-European language family made up?

A Riley Respite

Why didn't Thanos use the Time Stone to stop the Avengers' plan?

Caught 2 students cheating together on the final exam that I proctored

Why does this if-statement combining assignment and an equality check return true?

Boss wants me to falsify a report. How should I document this unethical demand?

Looking for a soft substance that doesn't dissolve underwater

What is a Centaur Thief's climbing speed?

Who will lead the country until there is a new Tory leader?

Construct a word ladder

The usage of "run a mile" in a sentence

Determining if an Asset is within 600 metres of a Site

SQL: IF clause within WHERE clauseLarge dataset (SQL to C#), long load time fixSQL to determine minimum sequential days of access?Using LIMIT within GROUP BY to get N results per group?How to determine which Geocoordinates are inside a defined Area?Inconsistent geolocation query results in SQLCompare a value to a potentially large data setSelect all points that are within a 100 metre radius of a POINT()sql spatial select linestring parts within polygon using stintersectionFind the length of a bounding rectangle polygon

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

1

I have 20k assets in specific locations in the city I live in and I would like to determine which of those assets are within 600 metres of around 250 sites. One asset can be within the radius of multiple sites. I would like an output of only those assets that are within the site radius excluding all other assets.

I have created geographic point columns for both the asset and site based on their known latitude and longitude in their respective tables. The code that I am using below works, but it is taking hours to calculate which is not feasible for future tasks.

I am using a LocalDB SQL Server and SSMS.

SELECT 
 Assets.Asset_ID, Assets.AssetPoint, Site.Site_ID, Site.SitePoint 
FROM 
 Assets 
CROSS JOIN 
 Site 
WHERE 
 (Assets.AssetPoint.STIntersects(Site.SitePoint.STBuffer(600)) = 1) 

I am hoping for a table that has assets aligned to sites computed in a few minutes.

sql sql-server geolocation geography

share|improve this question

edited Mar 24 at 9:40

marc_s

591k13211301278

asked Mar 24 at 3:58

KFOzKFOz

32

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I guess you need to create a bounding box for each; if the bounding boxes don't overlap, then those assets can be quickly eliminated
  
  – Strawberry
  Mar 24 at 6:36
  

  
  
  
  

add a comment | 

0

1

I have 20k assets in specific locations in the city I live in and I would like to determine which of those assets are within 600 metres of around 250 sites. One asset can be within the radius of multiple sites. I would like an output of only those assets that are within the site radius excluding all other assets.

I have created geographic point columns for both the asset and site based on their known latitude and longitude in their respective tables. The code that I am using below works, but it is taking hours to calculate which is not feasible for future tasks.

I am using a LocalDB SQL Server and SSMS.

SELECT 
 Assets.Asset_ID, Assets.AssetPoint, Site.Site_ID, Site.SitePoint 
FROM 
 Assets 
CROSS JOIN 
 Site 
WHERE 
 (Assets.AssetPoint.STIntersects(Site.SitePoint.STBuffer(600)) = 1) 

I am hoping for a table that has assets aligned to sites computed in a few minutes.

sql sql-server geolocation geography

share|improve this question

edited Mar 24 at 9:40

marc_s

591k13211301278

asked Mar 24 at 3:58

KFOzKFOz

32

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I guess you need to create a bounding box for each; if the bounding boxes don't overlap, then those assets can be quickly eliminated
  
  – Strawberry
  Mar 24 at 6:36
  

  
  
  
  

add a comment | 

I have 20k assets in specific locations in the city I live in and I would like to determine which of those assets are within 600 metres of around 250 sites. One asset can be within the radius of multiple sites. I would like an output of only those assets that are within the site radius excluding all other assets.

I have created geographic point columns for both the asset and site based on their known latitude and longitude in their respective tables. The code that I am using below works, but it is taking hours to calculate which is not feasible for future tasks.

I am using a LocalDB SQL Server and SSMS.

SELECT 
 Assets.Asset_ID, Assets.AssetPoint, Site.Site_ID, Site.SitePoint 
FROM 
 Assets 
CROSS JOIN 
 Site 
WHERE 
 (Assets.AssetPoint.STIntersects(Site.SitePoint.STBuffer(600)) = 1) 

I am hoping for a table that has assets aligned to sites computed in a few minutes.

sql sql-server geolocation geography

share|improve this question

edited Mar 24 at 9:40

marc_s

591k13211301278

asked Mar 24 at 3:58

KFOzKFOz

32

SELECT 
 Assets.Asset_ID, Assets.AssetPoint, Site.Site_ID, Site.SitePoint 
FROM 
 Assets 
CROSS JOIN 
 Site 
WHERE 
 (Assets.AssetPoint.STIntersects(Site.SitePoint.STBuffer(600)) = 1) 

sql sql-server geolocation geography

sql sql-server geolocation geography

share|improve this question

edited Mar 24 at 9:40

marc_s

591k13211301278

asked Mar 24 at 3:58

KFOzKFOz

32

share|improve this question

edited Mar 24 at 9:40

marc_s

591k13211301278

asked Mar 24 at 3:58

KFOzKFOz

32

edited Mar 24 at 9:40

marc_s

591k13211301278

edited Mar 24 at 9:40

marc_s

591k13211301278

asked Mar 24 at 3:58

KFOzKFOz

32

asked Mar 24 at 3:58

KFOzKFOz

32