Learning about SRIDs (and constructing geometries via SQL)

741
9
06-03-2022 08:50 AM
Bud
by
Notable Contributor

Edit: Mods, I suppose this post could be moved to the Data Management community (or whatever's appropriate).

What resources do you recommend for learning about SRIDs? SRIDs have always been a gap for me.

For example, when working with a FC using SQL, it's not clear to me if/when I would need to change the SRID for the output geometries.

  • If I extract points from a line FC via an SQL query:
    When constructing the resulting points using SQL, should I use the original SRID from the lines, or do I need a new SRID since the geometry type has changed? (from lines to points)
select 
    objectid,
--Side note: If anyone's wondering, this is why I convert to/from WKB: https://support.esri.com/en/Technical-Article/000011333
sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),sde.st_srid(shape)) points_shape -- 🡅 --Question: When constructing the resulting points, should I use the original SRID from the lines,
-- or do I need a new SRID since the geometry type has changed? (from lines to points) from lines --Note: The original SRID of the lines is 300051.

 

  • Or, if I remove the M-dimension from lines via an SQL query:
    Do I need to use a different SRID when constructing the non-M-enabled geometries via SQL?

 

Thanks.

0 Kudos
9 Replies
by Anonymous User
Not applicable

Just curious, where does SRID 300051 come from? A quick search doesn't show anything. Is that an implementation detail of the database you're using?

0 Kudos
Bud
by
Notable Contributor

That SRID 300051 comes from ArcMap. I'm not sure what the reasoning is behind it:

Bud_1-1654283936602.png

 

 

0 Kudos
by Anonymous User
Not applicable

After reading @George_Thompson's link, I think a general answer might be ok here. TLDR Just use the same SRID. I don't work out of a database in this manner so this answer is just from principle.

Assumptions:

1. Your underlying coordinate geometry is in the correct SRID. The projection or geographic coordinate system has been defined correctly.

2. You do not need to convert or transform geometry to accomplish your analysis.

3. Edit to add an assumption: All records in the table have the same SRID. Apparently records in a table don't have to share an SRID.

If these assumptions are true and you are changing the geometry type, just use the same SRID. If the feature geometry's coordinate system is unknown or incorrect, you would need to (re)Define it. If you need to use another SRID for an analytic purpose, you would need to Project it.

From the docs:

Unknown coordinate system with an ST_Geometry, SDEBINARY, PostGIS geometry, SDO_Geometry, or SQL Server geometry column

An SRID greater than 300,000 is added to the geodatabase system table.

 

Definitely wondering what's going on here. What does the XY Coordinate tab say?

Bud
by
Notable Contributor

Thanks. That helps.

Here's the XY Coordinate System tab of the original FC:

Bud_0-1654293117106.png

And here's the Domain, Resolution and Tolerance tab:

Bud_1-1654293147564.png

 

 

0 Kudos
Bud
by
Notable Contributor

I did a test just now where I created a new FC from scratch using Catalog (using all the default settings).

The SRID is 26917. So that seems more normal, yes?

Bud_3-1654293981230.png

Bud_4-1654293996746.png

Bud_5-1654294013346.png


Maybe there was just something something funky going on with my original FC.

0 Kudos
by Anonymous User
Not applicable

At least that new "Default" SRID corresponds to something more concrete in the registry.

Since you asked about learning, here are the basic questions I would want to answer for myself before proceeding.

- Is NAD 83 UTM 17N the expected coordinate system for this data?
- Are the real world features within the NAD 83 UTM 17N zone?

- Does an SRID 300051 feature class behave the same as a NAD 83 UTM 17N feature class during the same analytic process (e.g. your SQL query and comparing results)?

If the answers are all yes, that's good preliminary confidence to continue using the same SRID. Worth noting that best practice is to project all data into a common CRS.

George_Thompson
Esri Frequent Contributor

For SRID info, take a look here: https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/what-is-an-srid.htm  especially for the 300### ones

--- George T.
Bud
by
Notable Contributor

@George_Thompson Thanks!

Side note: Is there a chance there's a small typo in your comment? Should "3000###" be "300###"?
That's obviously not a big deal. I just thought there's a chance it could cause confusion for someone else reading this post.

0 Kudos
George_Thompson
Esri Frequent Contributor

Thanks for catching the extra "0". Post updated.

--- George T.
0 Kudos