How do you generate WKT from a graphic drawn on a map?

Question

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

Answer 1

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:

5. Select the polygon using the selection tool;
6. Edit | Copy Features;
7. 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

Answer 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):

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!

Answer 3

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

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.

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

Answer 4

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...

Answer 5

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

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'

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

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

(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...