<commit_before>xy-to-point-cursor.sql
===================
The anonymous pg/plsql scripts allows one to use the existing X and Y coordinates of a table to insert geometry into an empty feature class.  Uses ST_GeomFromText for PostGIS data or ST_point 	for Esri ST_Geometry features.

----------


REQUIRED:

Enterprise PostgreSQL geodatabase.

Existing table with X and Y column (sample SQL to create this below).

Empty feature class to receive the calculation of X, Y values (Create this in ArcGIS).


DOCUMENTATION:

[PostGIS (pg_geometry)][1]

[Esri(ST_Geometry)][2]


TESTING SAMPLE DATA:

Create the test.xy table containing the coordinate values.

		CREATE TABLE sde.test_xy
		(
		  name character varying(255),
		  x_coord numeric(38,8),
		  y_coord numeric(38,8)
		)
		
Insert some initial values into the new test_xy table created above.

		INSERT INTO sde.test_xy(name, x_coord, y_coord) 
		VALUES ( 'Iowa Heartland Development', 1601700.38827856, 592924.23589906);
		
		INSERT INTO sde.test_xy(name, x_coord, y_coord) 
		VALUES ('Lumbermans Wholesale Company', 2303800.33381338, 501097.06751965);
		
		INSERT INTO sde.test_xy(name, x_coord, y_coord) 
		VALUES ( 'Plain Talk Publishing', 1610249.93843664, 579937.75662273);
		
		INSERT INTO sde.test_xy(name, x_coord, y_coord) 
		VALUES ( 'Burlington Northern Sante Fe', 2180668.53184913, 359288.89378747);
		
		INSERT INTO sde.test_xy(name, x_coord, y_coord) 
		VALUES ( '800 22nd Avenue', 2160559.50579430, 618667.01873098);
		
Run one of the snippets	in xy-to-point-cursor.sql depending on which spatial type the target feature class uses.

[1]:http://resources.arcgis.com/en/help/main/10.2/index.html#/ST_Geometry_storage_in_PostgreSQL/002p0000006s000000/
[2]:http://www.postgis.org/docs/ST_GeomFromText.html
<commit_msg>add syntax highlighting and inline code snippets<commit_after>xy-to-point-cursor.sql
===================
The anonymous pg/plsql scripts allows one to use the existing X and Y coordinates of a table to insert geometry into an empty feature class.  Uses ST_GeomFromText for PostGIS data or ST_point 	for Esri ST_Geometry features.

----------


REQUIRED:

Enterprise PostgreSQL geodatabase.

Existing table with X and Y column (sample SQL to create this below).

Empty feature class to receive the calculation of X, Y values (Create this in ArcGIS).


DOCUMENTATION:

[PostGIS (pg_geometry)][1]

[Esri(ST_Geometry)][2]

Create an empty test_xy table in your PostGIS database.

```PLpgSQL
CREATE TABLE sde.test_xy
(
  name character varying(255),
  x_coord numeric(38,8),
  y_coord numeric(38,8)
)
```

Insert a few initial values into the test_xy table created above.

```PLpgSQL
INSERT INTO sde.test_xy(name, x_coord, y_coord)
VALUES ( 'Iowa Heartland Development', -95.85450821899968, 41.26032662300048);

INSERT INTO sde.test_xy(name, x_coord, y_coord)
VALUES ('Lumbermans Wholesale Company', -86.83733475399492, 36.157220097130846);

INSERT INTO sde.test_xy(name, x_coord, y_coord)
VALUES ( 'Plain Talk Publishing', -96.93516146812652, 42.78694730817284);

INSERT INTO sde.test_xy(name, x_coord, y_coord)
VALUES ( 'Burlington Northern Sante Fe', -97.32718700699968, 32.864737798000476);

INSERT INTO sde.test_xy(name, x_coord, y_coord)
VALUES ( '800 22nd Avenue', -86.80997891429467, 36.161404032870905);
```

Run one of the snippets	in xy-to-point-cursor.sql depending on which spatial type the target feature class uses.

### PostGIS (pg_geometry)
* first use arcmap to create an empty feature class in the sde database and name it 'points_pggeom'
```PLpgSQL
-- In this example, sde.points_pggeom is the target feature class to receive the calculation of the XY values.
DO
$$DECLARE
	pt_curse CURSOR FOR SELECT x_coord, y_coord FROM sde.test_xy;
	x_var numeric;
	y_var numeric;
BEGIN
	OPEN pt_curse;
LOOP
	FETCH pt_curse INTO x_var, y_var;
	EXIT WHEN NOT FOUND;
		INSERT INTO sde.points_pggeom (objectid, shape)
		SELECT sde.next_rowid('sde', 'points_pggeom'),
			   ST_GeomFROMText('POINT(' || cast(x_var as text)|| ' ' || cast(y_var as text) || ')''', 4326);
END LOOP;
CLOSE pt_curse;
END$$;
```
### Esri ST_Geometry

* first use arcmap to create an empty feature class in the sde database and name it 'points_stgeom'

```PLpgSQL
-- In this example, sde.points_stgeom is the target feature class to receive the calculation of the XY values.
DO
$$DECLARE
	pt_curse CURSOR FOR SELECT x_coord, y_coord FROM sde.test_xy;
	x_var numeric;
	y_var numeric;
BEGIN
	OPEN pt_curse;
LOOP
	FETCH pt_curse INTO x_var, y_var;
	EXIT WHEN NOT FOUND;
	    INSERT INTO sde.points_stgeom (objectid, geom)
		SELECT
		sde.next_rowid('sde', 'points_stgeom'),
		ST_point(x_var , y_var, 4326);
END LOOP;
CLOSE pt_curse;
END$$;
```

[1]:http://resources.arcgis.com/en/help/main/10.2/index.html#/ST_Geometry_storage_in_PostgreSQL/002p0000006s000000/
[2]:http://www.postgis.org/docs/ST_GeomFromText.html

{'fraction_non_alphanumeric': 0.10396548181631395, 'fraction_numerical': 0.08013149784261352, 'mean_word_length': 4.686915887850467, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5930102', 'n_tokens_mistral': 2066, 'n_tokens_neox': 1711, 'n_words': 531}