topic Re: SQL Views and Select by Location Issues in ArcGIS Enterprise Questions

SQL Views and Select by Location Issues

LoganSuhr_PSE — Wed, 30 Jun 2021 16:27:48 GMT

Hi Community,

I'm having an issue using 'Select by Location' in Pro between a shapefile and a view created in SSMS based on census blocks. I am successful in performing a Select by Location on the original blocks. But when contained in a view, no luck. I certainly feels like something I'm missing in the view.

Many related posts here are older and a fresh response felt like the next step.

Spatial References:

srs_id	x_offset	x_scale	y_offset	y_scale	z_offset	z_scale	m_offset	m_scale	organization	organization_coordsys_id
1	-400	1000000000	-400	1000000000	-100000	10000	-100000	10000	EPSG	4326
2	-400	1111948722	-400	1111948722	0	1	0	1	EPSG	4269
3	-400	1000000000	-400	1000000000	0	1	0	1	EPSG	4269

Spatial Tables:

table_schema	table_name	column_name	type_schema	type_name	srs_id
DBO	GDB_ITEMS	SHAPE	dbo	ST_MULTIPOLYGON	1
DBO	PROVIDERSUMMARY_POLY	SHAPE	dbo	ST_MULTIPOLYGON	3
DBO	TL_2010_US_TABBLOCK10	SHAPE	dbo	ST_MULTIPOLYGON	2

SQL View Design

SELECT tb.OBJECTID ,tb.Shape ,tb.GEOID10 ,s.NumProviders ,s.OtherTechnologies ,s.DSLProviders ,s.CableModemProviders ,s.OpticalProviders ,s.SatProviders ,s.FixedWirelessProviders ,s.BBandProviders ,s.BBandPlatforms10Down ,s.BBandPlatforms25Down ,s.Fixed10 ,s.Fixed100 ,s.Fixed500 ,s.Fixed1000 FROM dbo.TL_2010_US_TABBLOCK10 AS tb INNER JOIN dbo.ProviderSummaryByBlock AS s ON tb.GEOID10 = s.BlockCode

After the view creation (done in SSMS), I then registered the view with the database via the right-click menu option in Pro's Catalog pane.

Pro 2.8.0

SQL Server 14.0.3370.1

AGS 10.9

Thanks all!

Re: SQL Views and Select by Location Issues

TanuHoque — Wed, 30 Jun 2021 17:54:11 GMT

can you pls check if spatial index is created properly for TL_2010_US_TABBLOCK10 ?

Re: SQL Views and Select by Location Issues

LoganSuhr_PSE — Thu, 01 Jul 2021 15:55:54 GMT

@TanuHoque, looks good to me.

Re: SQL Views and Select by Location Issues

TanuHoque — Tue, 13 Jul 2021 19:12:30 GMT

@LoganSuhr_PSE

Sorry for the delayed response. Is it possible for you to share your data with me somehow? I can take a look.

thanks

Re: SQL Views and Select by Location Issues

LoganSuhr_PSE — Tue, 13 Jul 2021 22:01:35 GMT

@TanuHoque , no worries. I can certainly share the data. The data is huge. What is the best method to ship it off?

Re: SQL Views and Select by Location Issues

TanuHoque — Tue, 13 Jul 2021 22:11:17 GMT

can you reproduce this with a smaller dataset?