I'm using PHPRunner v10.91 (Build 41974 x64), PostGIS v12.10, and Firefox v127.0 as browser.

NOTE: This code would be better suited to be in a "Validate As" function, but since I'm using a Text Area (not a Text Field), that's not an option in v10, apparently. Then the error would focus the screen to the bad field, and highlight the field Title, box, and error message in red. That's a whole other issue.

Problem solved:

• I have a Geometry field in PostGIS (called "geom")
• I want to List and Edit it as a GeoJSON text ("geom_json"), but update it back to the "geom" field.
• NOTE: GeoJSON can contain invalid geometries, so I must validate both:
  • the GeoJSON format, and
  • the geometry
  
  
  
  

I'm providing two similar but slightly different solutions to this problem

1. In the Save button code.
2. In a Field Event ("Change") for the Text Area input box.

There's lots of debugging text still here that can be cleaned up.

Hope this is clear enough, and useful to someone else!

Notes / Setup:

• DB: I have a Geometry field in PostGIS (called "geom")
• Query tab: setup following rows in Query designer tab ("Output" checked for both):
  • geom
  • ST_AsGeoJSON(geom)
    • Alias: geom_json
    
    
    
    
  
  
  
  
• Fields tab:
  • geom field is unchecked
  • geom_json field is checked
    • and "List" and "Edit" columns are checked (at least)
    
    
    
    
  • Set Properties for geom_json ("..." button)
    • "Edit as" tab:
      • Select "Text area"
      • Check "Insert NULL values instead of empty strings"
      • Set your box "Height"
      
      
      
      
    
    
    
    
  
  
  
  
• Designer tab --> Edit page:
  • Added "geom_json" field
  
  
  
  

Save button code

NOTE: in this function:

• Only if there's an error, then $message is printed as the error message
• The Save button error message appears at the top of the page.
• Click on Save button to validate data and see error messages.
• To validate and see error messages: change the data, and click on Save button.

Interesting functions from below:

• ST_GeomFromGeoJSON - convert GeoJSON text to PostGIS geometry
• DB::LastError - PHPrunner's get last error message from SQL command
• ST_IsValidDetail - Check if PostGIS Geometry is a valid geometry, return error messages
• ST_ForcePolygonCCW - force outer-ring polygon coordinates to be counter-clock wise (CCW), and inner-ring coordinates to be clock-wise, because GeoJSON format requires it.

In the Events tab --> Edit page --> Before record updated

• After the following code:if ($values['geom_json'] == '')
{
$values['geom_json'] = NULL;
}
  
• Added the following:

One screenshot (Test data set #3):

Field Event ("Change") code

Note:

• The field event error message appears just below the Tooltip for the geom_json field.
• Debugging messages (console.debug) appear in the Browser Console window as "Debug" messages.
• Some of the code below is redundant, and appears just to make it as similar as possible to the code above.
• To validate and see error messages: change the data, and click outside the text area box.

To display error messages below the geom_json field text area box, add the following:

• Designer tab --> Edit page:
  • Just below Tooltip for "geom_json" field, Inserted following HTML Text:
    • <div id='geom_error_message' /></div>
    
    
    
    
  • Added following CSS to make message same colour as other error messages (not quite red):
    • Select this new HTML Text button --> click "Custom CSS" button:#geom_error_message {
color: #b94a48;
font-weight: bold;
}
      
    
    
    
    
  
  
  
  

Added the Change field event:

• Designer tab --> Edit page --> Select "geom_json" field --> Click "View as/Edit as" button:
  • "Edit as" tab, click "Field events" button
  • Add a "new handler" called "geom_json_event" for event "Change"
  
  
  
  
• Note: Once you've created the field event, you may find the code on the Events tab --> Field events... --> geom_json_event

Added (Modified) the code:

• Client Before code:

• Server code:

• Client After:

One screenshot (Test data set #2):

Test Data:

• Good:
  • (1) Valid GeoJSON, Valid Geometry
    • {"type":"Polygon","coordinates":[[[-129.07157621,64.005109936],[-129.07157621,70.001972143],[-124.865609743,70.001972143],[-124.865609743,64.005109936],[-129.07157621,64.005109936]]]}
    
    
    
    
  
  
  
  
• Bad:
  • (2) INVALID GeoJSON:
    • {"typePolygon","coordinates":[[[-129.07157621,64.005109936],[-129.07157621,70.001972143],[-124.865609743,70.001972143],[-124.865609743,64.005109936],[-129.07157621,64.005109936]]]}
    
    
    
    
  • Valid GeoJSON:
  • (3) INVALID Geometry - Last coordinate not same as first. No "Location":
    • {"type":"Polygon","coordinates":[[[101.0,64.005109936],[-129.07157621,70.001972143],[-124.865609743,70.001972143],[-124.865609743,64.005109936],[-129.07157621,64.005109936]]]}
    
    
    
    
  • (4) INVALID Geometry - Self-intersection. Location=intersection point:
    • {"type":"Polygon","coordinates":[[[20,20],[120,190],[50,190],[170,50],[20,20]]]}