SQL Spatial - Return single record

Question

SQL Spatial - Return single record

Andrew Ingall 80

Hello

I am trying to join various tables together to bring back individual polygons from a spatial database. The problem is that lines intersect multiple polygons, where i just want to bring one record back based on either the shape id or osid fields.

I have the following and tried group by, but that fails.

Thanks

Andy

sqlsnip

/*CREATE OR ALTER VIEW v_road_poly_maintenanceresp AS*/ SELECT  TRN_RAMI_MAINTENANCELINE.maintenanceresponsibility, TRN_RAMI_MAINTENANCELINE.maintenanceauthority_authorityn, TRN_FTS_ROADTRACKORPATH.SHAPE, TRN_RAMI_MAINTENANCELINE.OBJECTID, TRN_FTS_ROADTRACKORPATH.osid FROM   TRN_FTS_ROADTRACKORPATH INNER JOIN              TRN_RAMI_MAINTENANCELINE INNER JOIN              TRN_NTWK_STREET_RDLINKREF ON TRN_RAMI_MAINTENANCELINE.usrn = TRN_NTWK_STREET_RDLINKREF.usrn INNER JOIN              TRN_NTWK_ROADLINK_RDTRKPTHREF ON TRN_NTWK_STREET_RDLINKREF.roadlinkid = TRN_NTWK_ROADLINK_RDTRKPTHREF.roadlinkid ON TRN_FTS_ROADTRACKORPATH.osid = TRN_NTWK_ROADLINK_RDTRKPTHREF.roadtrackorpathid /*group by TRN_FTS_ROADTRACKORPATH.osid, TRN_RAMI_MAINTENANCELINE.maintenanceresponsibility, TRN_RAMIMAINTENANCELINE.IBJECTID, TRN_FTS_ROADTRACKORPATH.SHAPE*/ order by TRN_FTS_ROADTRACKORPATH.osid asc

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-29T22:48:05.96+00:00

For this sort of questions you help yourself enormously if you post CREATE TABLE statements for your table(s) and INSERT statements with sample data, sufficient to cover all angles of the problem, and the desired result given the test data. It also helps if you give a verbal explanation of the business rules so we know why you want that particular result.
Greg Low 1,980 Reputation points Microsoft Regional Director

2024-02-10T03:56:28.15+00:00

As Erland mentioned, it's really hard to help without some code that can be run to demonstrate the issue.
That said, I'm wondering if you are missing the use of a spatial aggregate like a UnionAggregate.

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-29T22:48:05.96+00:00

For this sort of questions you help yourself enormously if you post CREATE TABLE statements for your table(s) and INSERT statements with sample data, sufficient to cover all angles of the problem, and the desired result given the test data. It also helps if you give a verbal explanation of the business rules so we know why you want that particular result.
Greg Low 1,980 Reputation points Microsoft Regional Director

2024-02-10T03:56:28.15+00:00

As Erland mentioned, it's really hard to help without some code that can be run to demonstrate the issue.
That said, I'm wondering if you are missing the use of a spatial aggregate like a UnionAggregate.

Share via

SQL Spatial - Return single record

Your answer