4

All,

In order to do some quick analysis using SQL spatial, I'd like to generate spatial objects using the STGeomFromText / ST_GeomFromText function.

With point data, I can do this by geocoding an address and using desktop GIS to give me the X/Y, which I then turn into a point using a syntax like this:

declare @xyPoint geometry;
set @xyPoint = geometry::STGeomFromText('Point(3175288.989  1711307.592)', 2232);

(This will create a temporary point geometry at the X,Y coordinates above in the 2232 (CO State Plane Central coordinate system)... I can then use the temporary point to drive distance calculations, etc.)

My question: is there a way to do something similar with a polygon: maybe draw a graphic in a desktop GIS map (ArcMap, QGIS, etc.), get the WKT from that graphic, which I can then use in a query to create a polygon object using the STGeomFromText function?

Basically, this is the opposite of the Quick Draw plugin for QGIS

2

I have a rough, buggy ArcMap AddIn that I regularly use for diagnostics: WKQuery

It's a 10.0 AddIn, but I use it 10.1 and 10.2 with no trouble (well, the same amount of trouble that I have in 10.0).

But the first 3 buttons will let you draw on the map and dump the WKT to a text box in a dockable window (last button in the toolbar):
enter image description here

Again, it's buggy and I'm in between textbox controls (because couldn't work out a clean way to use the Scintilla component in an AddIn).

And some of the credit for WKT piece goes to blah238 because I swapped out the WKT conversion to DnrGps_Wkt. But I also didn't read the license so I'm not sure if I'm breaking any rules--I apologize in advance if I broke the rules!

enter image description here

  • Jay - unfortunately I get an error: "No GUI components found in this Add-In. Add-In version does not match." I'm running ArcGIS 10.1 SP1... hmmm... – DPSSpatial Oct 24 '14 at 15:51
  • It was a 10.0 version but maybe I messed up the project. I just updated the AddIn version to 10.1 and recompiled. New link to a 10.1 version in this comment: dl.dropboxusercontent.com/u/1156285/util/WKQuery.esriAddIn – Jay Cummins Oct 24 '14 at 16:23
  • Ahhh that's it! Works like a charm... I love how you can set your data frame SRS and the tool will report the SRS of the dataframe... such great tool!!! Thank you Jay!!! – DPSSpatial Oct 24 '14 at 16:26
3

I do this from time to time in QGIS:

  1. Add an OpenLayers layer (in this case OSM);
  2. Set the project projection to the desired coord system (i.e. WGS84 UTM 36S);
  3. Create a new temporary shapefile or Spatialite layer (tempWKT here), also set to the correct coord system;
  4. Start editing the temp layer and add the desired polygon:

enter image description here

  1. Select the polygon using the selection tool;
  2. Edit | Copy Features;
  3. Paste into a text editor, giving you the attribute table with the WKT text:

wkt_geom id name POLYGON((326815 8528822,327693 8520261,332303 8515651,343279 8517626,353597 8528383,342621 8536945,326815 8528822)) 1

  • Interesting! And good to know as there aren't tools to draw graphics in QGIS, so this is a good workaround! Thanks, @Simbamangu! – DPSSpatial Oct 24 '14 at 16:27
  • I just tried this and it works great - it will do us well when we're using QGIS/PostGIS... thanks again! – DPSSpatial Jun 16 '15 at 20:49
2

The Feature info tool of OpenJUMP shows also the WKT of selected features.

enter image description here

enter image description here

However, with some scripting it is possible to not only show the WKT but also to embed WKT into something else and more powerful.

Here comes a small OpenJUMP tool that takes the WKT of each feature from a layer and inserts it into Intersects query for Oracle Spatial.

Install OpenJUMP and save the following code as "Show_WKT.bsh" into the directory of Bean tools "\lib\ext\BeanTools".

import com.vividsolutions.jts.geom.*;
import com.vividsolutions.jump.feature.*;
import com.vividsolutions.jump.workbench.model.*;

    htmlFrame = wc.workbench.frame.outputFrame;
    htmlFrame.createNewDocument();
    htmlFrame.setTitle("WKT Output");
    fc = wc.getLayerNamePanel().getSelectedLayers()[0].getFeatureCollectionWrapper();
    for (Iterator i = fc.getFeatures().iterator(); i.hasNext();) {
        Feature feature = (Feature) i.next();
        Geometry geometry = feature.getGeometry();
            htmlFrame.addText("SELECT * \n"
            +"FROM ORACLE_TABLE ot \n"
            +"WHERE sdo_anyinteract (ot.geoloc,SDO_GEOMETRY('"
            +geometry
            +"',SRID))='TRUE';"
                );
    }
    wc.workbench.frame.flash(htmlFrame);
    htmlFrame.surface();
}

Start OpenJUMP and draw some features and test your new tool. It should be on a list in Customize - Bean Tools.

enter image description here

Select the tool from the menu and it will create Oracle queries which contain WKT of the features. enter image description here

  • If I'm not mistaken, the default cut'n'copy action in JUMP is to take the feature into the clipboard as WKT. So you can high light a feature, copy, then paste into another app as WKT. – Paul Ramsey Oct 25 '14 at 17:32
  • Right. I edited the answer to show that as a first option. I also edited the script to show an example about how to embed WKT to a complete SQL query. – user30184 Oct 25 '14 at 20:13
1

Openlayers have an nice example on generating WKT on the fly for drawn objects.

http://dev.openlayers.org/sandbox/docs/examples/wkt.html

I know that it is not a desktop version that you ask fore, but it is probably the simplest solution you can find...

  • that threw an error in the latest version of firefox... hmmm – DPSSpatial Oct 24 '14 at 16:26
1

The latest version of QGIS has an option to Create New Temporary Scratch Layer:

enter image description here

Choose the geometry type and coordinate system, and the layer is added to the map and an edit session is started.

Draw the feature type you're after (in my case a polygon) and use the identify tool to see the attributes.

Right-click on the 'feature id' node, and choose 'Copy Feature'

enter image description here

Now in PGAdmin, or even QGIS DB Manager, paste the result:

enter image description here

And with a little SQL Spatial you can turn this into a geometry object:

enter image description here

(note I've added 4326 as the coordinate system I use, which is also the coordinate system I used to create the temporary scratch layer)

Now you can use this to build a geometry object you can run spatial queries against, for example:

with polygon as (

select ST_GeomFromText('
Polygon ((-105.03792611059080286 39.78014782225491786, -105.04818400099962616 39.75856265597848704, -105.02284438556741009 39.75418720873850731, -105.01231287864754904 39.76789982851657612, -105.01364722199988933 39.78389171288461768, -105.03792611059080286 39.78014782225491786))', 4326)
 as geom) 

select
* from public."Schools_Current" as sch
, polygon 
where ST_Intersects(sch.geom, polygon.geom)

...which returns the schools that fall into the polygon object I've created...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.