A Spatial View to calculate XY coordinates

HaniDraidi

I have a points layer with X,Y fields, each time I add a point I should perform "calculate geometry" to calculate the coordinates of the new points.

Is there a way to handle this automatically via a spatial view in SQL Server using a certain geometry function?

Any help is appreciated!

KenGalliher1

This is typically done using a SQL trigger that listens for incoming geometries either from editing a map document or inserting geometries with SQL.  When using a native DBMS spatial type such as SQL Server Geometry or Postgres' PostGIS type, constructor functions and spatial operator functions are provided that allow one to query different spatial properties.

For instance, in SQL Server we can use the

shape.Lat, shape.Long

functions for Geography types or

shape.X and shape.Y

for Geometry types on a spatial column called "shape".

Here is a simple SQL Server Geography example using shape.Lat and shape.Long. to update columns called "latitude" and "longitude" with their respective values.  This sample is provided AS IS and must be properly tested before implementing in a production environment.

<SPAN class="keyword token">CREATE</SPAN> <SPAN class="keyword token">TRIGGER</SPAN> <SPAN class="punctuation token">[</SPAN>gis<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>Insert_xy_if_geometry_fc<SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">ON</SPAN> <SPAN class="punctuation token">[</SPAN>gis<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>Landmark_fc<SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">AFTER</SPAN> <SPAN class="keyword token">INSERT</SPAN>
&nbsp; <SPAN class="punctuation token">,</SPAN><SPAN class="keyword token">UPDATE</SPAN>
<SPAN class="keyword token">AS</SPAN>
<SPAN class="keyword token">UPDATE</SPAN> pt
<SPAN class="keyword token">SET</SPAN> pt<SPAN class="punctuation token">.</SPAN>latitude <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>Shape<SPAN class="punctuation token">.</SPAN>Lat
&nbsp; <SPAN class="punctuation token">,</SPAN>pt<SPAN class="punctuation token">.</SPAN>longitude <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>Shape<SPAN class="punctuation token">.</SPAN>Long
<SPAN class="keyword token">FROM</SPAN> Landmark_fc <SPAN class="keyword token">AS</SPAN> pt
<SPAN class="keyword token">INNER</SPAN> <SPAN class="keyword token">JOIN</SPAN> <SPAN class="punctuation token">(</SPAN>
&nbsp; <SPAN class="keyword token">SELECT</SPAN> objectid
&nbsp; <SPAN class="punctuation token">,</SPAN>Shape
&nbsp; <SPAN class="keyword token">FROM</SPAN> inserted
&nbsp; <SPAN class="keyword token">WHERE</SPAN> <SPAN class="punctuation token">(</SPAN>Shape <SPAN class="operator token">IS</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="token boolean">NULL</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp; <SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">AS</SPAN> i <SPAN class="keyword token">ON</SPAN> i<SPAN class="punctuation token">.</SPAN>objectid <SPAN class="operator token">=</SPAN> pt<SPAN class="punctuation token">.</SPAN>objectid<SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">RETURN</SPAN>
GO
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Lat (geography Data Type)

Long (geography Data Type)