0

I have a table containing a list of properties.
I have a successful query to return only those that intersect another layer.
However i want to return all the properties now and instead return a new column that shows InBuffer = True or False.

This query works to get only the intersections but dont know how to change it to what i need. Any ideas?

SELECT p.[FEATURE_ID] AS [FEATURE_ID],
  p.[Transactionuniqueidentifier] AS [Transactionuniqueidentifier],
  p.[geogtab] AS [geogtab],
  p.[Price] AS [Price],
  r.[linenum] AS [linenum]
FROM [dbo].[GB_PP_HS2_1617] p
inner join [dbo].[GB_HS2_ROUTE_C] r 
on r.railroute.STBuffer(500).STIntersects(p.geogtab) = 1

In this example geogtab is a geography column.

If this is easier to perform in POSTGIS im happy to use that instead.

I have tried the following without success.... query runs forever without finishing

    SELECT p.[FEATURE_ID] AS [FEATURE_ID],
 p.[Transactionuniqueidentifier] AS [Transactionuniqueidentifier],
 p.[geogtab] AS [geogtab],
 r.geog.STBuffer(500).STIntersects(p.geogtab) AS [InBuffer],
 r.[linenum] AS [linenum]
FROM [dbo].[GB_PP_HS2_1617] p
left join [dbo].[GB_HS2_ROUTE_C] r 
on r.geog.STBuffer(500).STIntersects(p.geogtab) = 1
1

You can add a case statement in your select that basically tests the same as the STIntersects() function:

 SELECT p.[FEATURE_ID] AS [FEATURE_ID],
 p.[Transactionuniqueidentifier] AS [Transactionuniqueidentifier],
 p.[geogtab] AS [geogtab],
 r.geog.STBuffer(500).STIntersects(p.geogtab) AS [InBuffer],
 r.[linenum] AS [linenum],

case when r.geog.STBuffer(500).STIntersects(p.geogtab) = 1 then 'True' else 'False' end as intersects_status

FROM [dbo].[GB_PP_HS2_1617] p
left join [dbo].[GB_HS2_ROUTE_C] r 
on r.geog.STBuffer(500).STIntersects(p.geogtab) = 1
| improve this answer | |
1

In PostGIS you wouldn't do that with buffer and st_intersects. Instead you should use ST_DWithin. That function uses indexes to do a first check with help of index and then recheck if there is actually an intersection.

Also, since you do the intersects test in the join you don't have to do it in the select part (it shouldn't cost anything since st_intersects is an immutable function but anyway). Instead you can just check if some column in the second table (if left join) is null (meaning there is nu intersection).

I would also put the result into a table at once and analyze it from there. Too often slow queries is just massive data amounts that takes time to send and show in the client.

So your query would look someting like:

CREATE INDEX idx_GB_PP_HS2_1617_geom on "dbo"."GB_PP_HS2_1617" using gist(geogtab);
CREATE INDEX idx_GB_HS2_ROUTE_C_geom on "dbo"."GB_HS2_ROUTE_C" using gist(geog);

analyze;

CREATE TABLE result AS
SELECT p."FEATURE_ID" AS "FEATURE_ID",
 p."Transactionuniqueidentifier" AS "Transactionuniqueidentifier",
 p."geogtab" AS "geogtab",
 (r."linenum" is not null)::text AS "InBuffer",
 r."linenum" AS "linenum"
FROM "dbo"."GB_PP_HS2_1617" p
left join "dbo"."GB_HS2_ROUTE_C" r 
on ST_DWithin(r.geog, p.geogtab, 500);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.