25
votes
Accepted
Is it ok to group by geometry in PostGIS?
There's almost certainly nothing wrong with grouping by geometry in this case, since you're already grouping by a unique ID (nbhd_id). And, as you point out, it saves you a join and makes the query ...
- 13.2k
11
votes
Why do single-part WKT polygon vertices require double parentheses?
It is because polygons can have multiple rings in them, the exterior and a list of interior rings or holes. To save parsers having to guess if there are holes by the number of brackets there are ...
- 82.7k
10
votes
Accepted
Converting raster layer to simple feature in R
Following https://r-spatial.github.io/stars/articles/stars5.html#vectorizing-a-raster-object-to-an-sf-object yo can do it with stars, although the question is not completely clear for me:
library(...
- 1,366
10
votes
Why do single-part WKT polygon vertices require double parentheses?
@user30184 already gave the short answer in the comments here. For a longer answer, one needs to look to the Simple Feature Access - Part 1: Common Architecture | OGC standard.
The Well-known Text (...
- 2,290
6
votes
Accepted
Convert geometry set to simple features collection using `sf` in R
I think you want to create a spatial data frame with st_sf:
nc2 = st_sf(geom=nc_geom)
class(nc2)
#[1] "sf" "data.frame"
Then you can add extra columns:
nc2$ID=1:length(nc_geom)
nc2
#Simple ...
- 65.6k
4
votes
Accepted
Snap a point to a line and get the line ID (Oracle Spatial or SDE.ST_GEOMETRY)
This example uses Oracle Spatial's Linear Referencing facility to project the inspection points onto the closest sidewalk like. It then updates the inspection point with:
The id of the closest ...
- 2,898
3
votes
Snap a point to a line and get the line ID (Oracle Spatial or SDE.ST_GEOMETRY)
Here is the answer from my side using different functions with the trigger.
Create Table.
create table linestrings (
id number primary key,
geom sdo_geometry
);
create table inspection (
id ...
3
votes
Convert X & Y columns to a SHAPE column (using a method that would perform well in Portal)
I assume that there is a physical Oracle table that sees insert/update events as the REST service fires.
And that those events aren't a complete truncate/reload.
If so I would create a before insert/...
3
votes
Accepted
Convert PostgreSQL geometry to Esri ST_Geometry
While PostGIS uses Well-Known Binary internally, you'd be better off explicitly casting the PostGIS geometry to Well-Known Text or Binary.
Try
SELECT a.objectid,
a.shape,
b.objectid AS ...
- 20.2k
3
votes
How is the st_length function stored in an Oracle geodatabase?
length is stored with the shape so there is no call to the extproc
- 785
3
votes
function st_within(sde.st_geometry, geometry) does not exist
You are mixing up PostGIS spatial columns and sde spatial columns. Your 'shape' column is using the Esri spatial type. Your ST_GeomFromText() function is creating a PostGIS spatial type. And the ...
- 19.9k
3
votes
ST_GEOMETRY points: get attribute of intersecting polygon
I think I figured it out. I just hadn't done a spatial join with SQL until now.
I ended up doing the spatial join on a line FC instead of a point FC (but I convert the lines to midpoints in the ...
- 1,222
2
votes
Get ArcGIS to recognize ST_POINT
I don't have an SDE to play with. I think you would have to do something like this:
With point As (
select sde.st_point (10.01, 20.03, 4326) as pt from dual
)
select sde.st_geometry(sde.ST_X(p.pt), ...
- 449
2
votes
Accepted
Get XML from Oracle sde.st_geometry SHAPE column using SQL
There is no XML within SDE.ST_GEOMETRY. Furthermore, neither the SDE.ST_GEOMETRY type nor the underlying st_geometry DLL, which implements that custom type, nor even the SgShape library beneath that ...
- 20.2k
2
votes
Accepted
What is ST_Geometry, ST_Curve and ST_Surface on PostGIS?
The ST_Geometry type is the maximal supertype of the geometry type hierarchy, ST_Curve and ST_Surface are direct subtypes.
The types you list are further down the line (more specialized). You can ...
- 911
2
votes
Accepted
STContains always returns 0 when i query from table. But same data works when I manually create a polygon geometry object and select
If you execute this query, you should get 1 and 1. As a comment, you probably would like to use a meaningful spatial reference system id such as WGS84 (WKID 4326) instead of just 0 when you construct ...
- 30k
2
votes
st_geometry syntax for Oracle Sql
I figured out the syntax.
WITH cons
AS (SELECT DISTINCT
culvertgid,
condition AS condition_code,
VALUE AS condition_literal
FROM culvert_inspect
...
- 31
2
votes
Creating point from lat / lon values stored in two fields?
What do you mean by "stored in two fields" ? Are they in another table ? If so you need to use an INSERT ... SELECT approach.
Assume you have a table with the values like this:
create table pts (id ...
- 2,898
2
votes
Accepted
How to find nearest neighbor in Oracle's ST_GEOMETRY?
First you will need to convert st_geom to wkt and then create sdo_geom from wkt, something like this should work.
select SDO_UTIL.FROM_WKTGEOMETRY(sde.st_astext(shape),SRID) from table;
you can also ...
- 1,634
2
votes
Accepted
Use SDE.ST_GEOMETRY functions in a custom function
I am surprised all the SDE experts did not already chime in.
Your title shows the problem and your description provides the proper answer, so you are already all the way there.
SDE.ST_SRID is not ...
- 680
2
votes
Convert X & Y columns to a SHAPE column (using a method that would perform well in Portal)
Number 1 will have least overhead. Based on map extent used most often set smallest grid slightly smaller than this map extent zoomed to. Set next middle grid 3 times this value. Set largest grid ...
2
votes
Materialized view with FAST refresh on remote table: How to include a SHAPE column?
I have a few options. I can create the fast refresh MV on a remote table without the SHAPE column, but then add the SHAPE column afterwards.
A. Add SDO_GEOM column & trigger
Create an ...
- 1,222
2
votes
What system tables get INSERTS when registering object with Oracle EGDB (ST_Geometry)?
GDB_ITEMS,
TABLE_REGISTRY,
LAYERS,
COLUMN_REGISTRY,
GEOMETRY_COLUMNS,
ST_GEOMETRY_COLUMNS (if using ST_GEOMETRY storage type)
- 72
2
votes
Accepted
ST_GEOMETRY: spatial column must appear immediately after spatial relationship function for spatial index to be used
This page from the docs explains it better:
What is the correct order for the st_geometry relational operator input arguments in Oracle?
Specifying the correct argument order for the st_geometry ...
- 1,222
2
votes
What densification method does SDE.ST_Geometry use to approximate arcs as segments?
Densify GP (a related operation):
We can refer to the Densify geoprocessing tool which appears to perform a similar operation to the segment approximation that SDE.ST_Geometry does.
Curve segments ...
- 1,222
2
votes
Accepted
Editing vertex without removing true curves using ArcPy
I guess you can manipulate it using JSON.
For example, this field calculator expression, when I run it on a buffer of a point:
import json
def wJson(shp):
pnts = json.loads(shp.JSON)
#esriJSON = ...
- 23.3k
2
votes
How many vertices does a SDE.ST_GEOMETRY rectangle have?
It looks like the rectangle actually has five vertices:
--SQL Query:
select
(shape).numpts
from
my_polygon_fc
(SHAPE).NUMPTS
--------------
5
My guess at an explanation:
There ...
- 1,222
1
vote
Converting SDE.ST_GEOMETRY polyline to SDO_GEOMETRY with SQL: Why are there so many nulls?
I have seen some application software, like TOAD, display Oracle VARRAYs in ways like this.
The SDO_GEOMETRY may be fine which is why I suggest querying it for sdo_util.GetNumVertices (tests ...
- 449
1
vote
Converting SDE.ST_GEOMETRY polyline to SDO_GEOMETRY with SQL: Why are there so many nulls?
I have access to Oracle not ESRI so I can't help with all aspects.
The WKT looks fine, though the space after the first ( is normally not there.
Try using the sdo_geometry(WKT,srid) constructor ...
1
vote
create spatial view in postgis on an ArcGIS table with ST_Geometry type
As per suggestions in the comments above, I have decided to convert my spatial table created with ArcGIS from ST_Geometry to PG_Geometry. After doing that, I was able to simply use a ST_Collect(a....
- 41
Only top scored, non community-wiki answers of a minimum length are eligible