Using geometry variable in PostgreSQL/PostGIS statements -

I am trying to write a UPSER statement to include or update a line in a PostgresSQL database with geometry columns. My input is a KML piece and the following statement works for me until the KML is valid.

  UPDATE area SET size = ST_GeomFromKML ('{KML}') WHERE area_code = '{area}'; In the field, select Insert (regionicode, shape) 'field (region)', ST_GeomFromKML ('{KML}') where not present (Select area from area WHERE area_code = '{0}');  

If this is relevant, then I am calling it from a C # ASP.NET MVC application using a SQL command object, but as long as the SQL statement is not correct It should not have any effect till

I want to use the change and to ensure that the column is correct. Unfortunately, the experience of my recent database is mostly SEOLearers, in which some mysql and postgres do not seem to handle the same variables with SQL statements.

For something like what I like:

  DECLARE @shape geometry; SELECT @shape = ST_GeomFromKML ('{KML}'); IF No (STIISWalid (@ size)) SELECT @shape = ST_MakeValid (@shape); UPDATE area SET size = @shape WHERE area = '{area}'; In the field, select Area (Region, Shape), select 'Area (region)', @ size where it is not present (select area with area WHERE area_code = '{0}');  

So that I am checking validity and correcting it once in the code. However, even after reading the document, I do not understand how to use variables to do this.

In PostgreSQL you need to write a stored procedure in PL / pgSQL language (assuming that your Km L piece and "zone" are the string):

  create myFunc (km list, zip text) $$ DECLARE transforms zero as SHP geometry; BEGIN shp: = ST_GeomFromKML (km); If not (STIISAAIGID (SPP)) SHP: = STMACWalid (SPP); end if; Updated area SET size = SHP WHERE field area = zip; Insert area (area area, shape) Zip, where SHP is not present (Select area from area WHERE area_code = zip); End; $$ LANGUAGE plpgsql;  

Documentation can be found on PL / pgSQL.