SQL Views and Select by Location Issues

714
5
06-30-2021 09:27 AM
LoganSuhr_PSE
New Contributor III

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_idx_offsetx_scaley_offsety_scalez_offsetz_scalem_offsetm_scaleorganizationorganization_coordsys_id
1-4001000000000-4001000000000-10000010000-10000010000EPSG4326
2-4001111948722-40011119487220101EPSG4269
3-4001000000000-40010000000000101EPSG4269

 

Spatial Tables:

table_schematable_namecolumn_nametype_schematype_namesrs_id
DBOGDB_ITEMSSHAPEdboST_MULTIPOLYGON1
DBOPROVIDERSUMMARY_POLYSHAPEdboST_MULTIPOLYGON3
DBOTL_2010_US_TABBLOCK10SHAPEdboST_MULTIPOLYGON2


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!

 

0 Kudos
5 Replies
TanuHoque
Esri Regular Contributor

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

0 Kudos
LoganSuhr_PSE
New Contributor III

LoganSuhr_PSE_0-1625154881345.png

@TanuHoque, looks good to me.

0 Kudos
TanuHoque
Esri Regular Contributor

@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

 

0 Kudos
LoganSuhr_PSE
New Contributor III

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

0 Kudos
TanuHoque
Esri Regular Contributor

can you reproduce this with a smaller dataset?

0 Kudos