I have a simple table with a geometry column like so:

CREATE TABLE geomtable 
(
  gid serial, 
  geom geometry, 
  trip_count integer
);

I want a unique constraint on the geometry column, such that if the same geometry is being inserted again then, the ON CONFLICT clause should add the trip_count to the existing row. So my insert query looks something like this:

INSERT INTO geomtable (geom, trip_count) values (, 123) 
ON CONFLICT ON CONSTRAINT 
DO UPDATE SET trip_count = geomtable.trip_count + EXCLUDED.trip_count;

I tried the approach to enforcing equality constraint as described here like so:

CREATE FUNCTION equality_constraint_func(
  id INT,
  gm GEOMETRY
)
RETURNS boolean AS
  $$
    SELECT NOT EXISTS (
      SELECT 1
      FROM   geomtable AS a
      WHERE  a.gid  id
        AND  a.geom && gm
        AND  ST_Equals(a.geom, gm)
    );
  $$
  LANGUAGE sql
;

And adding a CHECK constraint like so :

ALTER TABLE geomtable
  ADD CONSTRAINT equality_constraint
  CHECK (equality_constraint_func(gid, geom))
;

**However, this does not help me perform the DO UPDATE part of conflict resolution.** It just raises an error on duplicate geometry. I of course can't create a primary key out of my geom column as I get the index row requires 42632 bytes, maximum size is 8191 error. I get a similar error on creating a unique index on the geom column. I tried creating a unique constraint using the above syntax by replacing CHECK with UNIQUE, but I get a syntax error. How would I go writing a trigger to enforce the unique constraint? Specifically, how do I handle the conflict case? Let the insert happen as usual and then perform a delete etc. My version information is as under:

PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"

I have an Oracle 18c table that has an SDO_GEOMETRY column (lines): create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry); insert into a_sdo_geometry_tbl (line_id, shape) values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658)) ) ---------------- select line_id, sdo_util.to_wktgeometry(shape) as well_known_text from a_sdo_geometry_tbl; LINE_ID WELL_KNOWN_TEXT -------------------------------------------------------------------------------- 1 LINESTRING (671539.685273438 4863324.18143614, 671595.050070336 4863343.16655619, 671614.013553706 4863350.34348304, 671622.204415338 4863353.52539613) 2 LINESTRING (71534.5567096211 4863119.99180975, 671640.738468866 4863157.13274525, 671684.86211504 4863172.02299559) 3 LINESTRING (671622.204415338 4863353.52539613, 671633.326716411 4863357.84622911, 671904.061407769 4863451.28616675) 4 LINESTRING (671684.862052112 4863172.02299559, 671892.149614432 4863244.14144007, 671951.21565712 4863264.82431039, 671957.447146119 4863266.84761768, 671966.824385692 4863269.14663266) 4 rows selected. -------------------------- For each line, I want to select each vertex as a separate row in a query/resultset. I want to do this via a **recursive WITH clause** -- without using the GetVertices() function or a custom TYPE. Is there a way to do that? ---------------------- The resultset would look like this: LINE_ID VERTEX_ID X Y ---------- ---------- ---------- ---------- 1 1 671539.685 4863324.18 1 2 671595.050 4863343.17 1 3 671614.014 4863350.34 1 4 671622.204 4863353.53 2 1 71534.5567 4863119.99 2 2 671640.738 4863157.13 2 3 671684.862 4863172.02 3 1 671622.204 4863353.53 3 2 671633.327 4863357.85 3 3 671904.061 4863451.29 4 1 671684.862 4863172.02 4 2 671892.150 4863244.14 4 3 671951.216 4863264.82 4 4 671957.447 4863266.85 4 5 671966.824 4863269.15 Hints: The following functions might be useful: PointN: Returns a point that is the nth vertex in the collection of vertices GetNumVertices: Returns the number of vertices in the input geometry. ----------------

I have a database, call it **maps**, which contains 2 schemas, **a** & **b**. In each schema I have a number of tables: **a.t1**, **a.t2**, **b.t1**, **b.t2** (plus others). The column sets on each of these tables is different, but there are a number of columns in common. I have defined a materialised view, **a.mv**, which brings in the common columns of the 4 tables listed, including a geometry column (which represents a geographic outline). I want to backup the current contents of the view so that I can restore it on another server. To do this, I use this pg_dump command: pg_dump -h hostname -U username -t a.mv -f mv.sql maps What I get as a result is the SQL to define the table, but no data. The view definitely has data in it, because I can select from it in PgAdmin (it was created multiple days ago and the underlying tables haven't been changed since) I can dump the underlying tables, including data, with (eg) pg_dump -h hostname -U username -t a.t1 -f t1.sql maps but not the view. From the limited matches I've found with googling this, what I'm trying should work, but I'm wondering if the presence of a geometry column in the dump is causing the issue (or this might be a complete red herring). FWIW, the total data in the view is fairly substantial - probably around 1GB. However, I've dumped all the underlying tables in schema **a** successfully (including the 2 tables referenced by the view, and others), which was larger (1.5GB) Any ideas what the issue could be here? On the face of it, what I'm trying to do should work, but just doesn't and with no indicated errors.

I have a table parcels where I store polygons in a column. I'm trying to find all parcels that intersect with a user-provided polygon and calculate the intersection area in square meters. The table: CREATE TABLE pacels ( uuid binary(16) NOT NULL COMMENT '(DC2Type:uuid)', geometry polygon NOT NULL /*!80003 SRID 25830 */ COMMENT '(DC2Type:geography_polygon)', PRIMARY KEY (uuid), SPATIAL KEY idx_geometry (geometry) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci Here is my query: $sql = sprintf( "SELECT *, ST_AsBinary(geometry) AS geometry, " . "ST_Area(ST_Intersection(geometry, ST_GeomFromText('POLYGON((%s))', 25830))) AS overlap_area " . "FROM parcels " . "WHERE ST_Intersects(geometry, ST_GeomFromText('POLYGON((%s))', 25830)) ", $polygon, ); The SRID of all polygons in the table and the provided polygon is 25830. There is a spatial index on the geometry column. I'm using Mysql 8.0.40 When I include the overlap_area in the query, I get the following error: SQLSTATE[22S01]: >: 3516 POLYGON/MULTIPOLYGON value is a geometry of unexpected type GEOMCOLLECTION in st_area. If I remove the ST_Area calculation, the query runs fine and returns the intersecting parcels, but I obviously can't calculate the intersection area. - How can I correctly calculate the intersection area? - Is 25830 the correct SRID for this use case? Most parcels are located in mainland Spain, and the operations I need to perform involve intersection queries and area calculations. Any help is greatly appreciated!

We've got to store information about a diversity of mechanical "arms" that have a certain reach profile. We need to store the information about what kind of reach profile the arm has. Since there are a multitude of joints in the arms, the reach is not a perfect parabola, it could look as follows. The porpuse of the data in our database is for a "webshop" to be able to filter on horizontal reach versus vertical reach. As you can imagine, when a certain vertical reach has been entered, there is only a certain horizontal reach on that height. What would be the best format for storing this data? We're allowed to have a "resolution"-aspect of this, so the outcome of filtering this data does not have to be perfect.

My situation is somewhat similar to [this earlier question](https://dba.stackexchange.com/questions/281629/geospatial-join-using-st-contains-or-st-within-seems-slow-with-costly-query-and) : In PostgreSQL I have one table containing point geometries (P), and another containing polygons (G). I would like to join on the geometry field, e.g.

SELECT P.ID, G.SHAPE
FROM POINTS P
JOIN NL_1KM G ON ST_CONTAINS(G.SHAPE, P.SHAPE)

The difference is that my polygons are a rectangular 1×1 km grid, so I don't think ST_Subdivide will help. The grid table has ~126,000 records, and there are currently ~280 points. Both tables have a spatial index, all geometries are WGS84. I would think that only 280 lookups on the grid would be required, but this takes roughly one minute. The same query with a 10km grid (1350 records) takes only 0.6 seconds This is the query plan for the 1km grid:

QUERY PLAN
Nested Loop  (cost=0.00..9816799.62 rows=12459941 width=600) (actual time=2964.853..66014.294 rows=284 loops=1)
  Output: w.id, g.shape
  Join Filter: st_contains(g.shape, (w.shape)::st_geometry)
  Rows Removed by Join Filter: 37505400
  ->  Seq Scan on myschema.nl_1km g  (cost=0.00..4571.58 rows=125858 width=596) (actual time=0.025..41.477 rows=125858 loops=1)
        Output: g.objectid, g.cellcode, g.eoforigin, g.noforigin, g.shape
  ->  Materialize  (cost=0.00..24.45 rows=297 width=484) (actual time=0.000..0.012 rows=298 loops=125858)
        Output: w.id, w.shape
        ->  Seq Scan on myschema.points w  (cost=0.00..22.97 rows=297 width=484) (actual time=0.007..0.106 rows=298 loops=1)
              Output: w.id, w.shape
Planning Time: 0.293 ms
Execution Time: 66014.393 ms

Note that the st_geometry data type in the execution plan is an ArcGIS data type. What am I missing? ### Update, a few minutes after I posted... One thing I was missing is that using ST_Intersects instead of ST_Contains makes things so fast, it almost scares me (0.06 sec). The new query plan shows that the spatial index on the grid is now used. So, my question should have been, why does ST_Contains not use the spatial index? If anyone has an answer to that?

We have a table containing all polygons (geometry/geography) of the objects in our application. This is because most polygons need to have a 'geometry' ([RDNew](https://epsg.io/28992)) and a 'geography' ([WGS84](https://epsg.io/4326)) version of the same polygon AND need to be reused (most polygons are used in multiple objects). To make sure the correct reference is added to an object (based on the polygon which needs to be associated with that object), we first seek the polygon in the polygon table using the query:

declare @__geometry_0 sys.geometry -- filled by a polygon value

SELECT TOP(1) [g].[Id], [g].[AangemaaktOp], [g].[OorspronkelijkeCoordinaatSysteem], [g].[RDNewGeo], [g].[RDNewOppervlakte], [g].[WGS84Geo], [g].[WGS84Oppervlakte]
FROM [GeoData] AS [g]
WHERE [g].[RDNewGeo].STEquals(@__geometry_0) = CAST(1 AS bit)
ORDER BY [g].[Id]

We are running MS SQL Server as an Azure 'service'. The problem we are running into is that these search actions take about 2 seconds each (at this point there are 250.000+ polygon records in the table). So we want to reduce the time it takes to locate a (one) specific polygon. At first, we noticed there was no spatial index. so we added one to see how much it would speed up the search.

CREATE SPATIAL INDEX IX_SPATIAL_GeoData_RDNewGeo ON dbo.GeoData(RDNewGeo)
WITH( BOUNDING_BOX  = ( xmin  = 0.0, ymin  = 300000.0, xmax  = 280000.0, ymax  = 625000.0) )

However, when running the above query, the index is still not used (when viewing the actual execution plan). So second, we modified the query NOT to use the top (1) statement, and now the spatial index is used (according to the execution plan). However, now the search is even slower! *EDIT: after retesting, we didn't notice the slowdown anymore, but on the contrary a small performance gain (locally) => see the answer to this question for the complete explanation.* Any ideas on how to speed up the execution of a query that searches for one specific polygon (or are we just going the wrong way about this)? *Additional info:* SQL-server: Microsoft SQL Azure (RTM) - 12.0.2000.8 Create table statement

CREATE TABLE [dbo].[GeoData](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[AangemaaktOp] [datetime2](7) NOT NULL,
	[OorspronkelijkeCoordinaatSysteem] [smallint] NOT NULL,
	[RDNewGeo] [geometry] NOT NULL,
	[RDNewOppervlakte] [float] NOT NULL,
	[WGS84Geo] [geography] NOT NULL,
	[WGS84Oppervlakte] [float] NULL,
 CONSTRAINT [PK_GeoData] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sample polygon

POLYGON ((128864.429 449604.427, 128863.868 449605.748, 128863.48 449605.51, 128862.98 449605.33, 128862.47 449605.24, 128861.94 449605.25, 128861.43 449605.37, 128861.16 449605.49, 128860.11 449605.46, 128858.84 449605.43, 128856.44 449604.98, 128777.65 449582.27, 128692.42 449557.94, 128628.68 449540.22, 128547.49 449517.35, 128487.491 449500, 128443.71 449487.34, 128443.43 449487.36, 128443.22 449487.42, 128443.03 449487.51, 128442.85 449487.65, 128442.71 449487.81, 128442.6 449488, 128442.598 449488.006, 128433.38 449485.18, 128433.41 449484.9, 128433.39 449484.62, 128433.32 449484.35, 128433.19 449484.1, 128433.02 449483.87, 128432.81 449483.68, 128432.58 449483.54, 128427.179 449481.36, 128425.795 449480.801, 128427.467 449475.951, 128427.11 449473.74, 128427.51 449473.46, 128427.84 449473.11, 128428.12 449472.7, 128438.58 449441.28, 128438.98 449440.73, 128439.48 449440.25, 128440.07 449439.88, 128440.71 449439.62, 128441.4 449439.48, 128442.09 449439.48, 128442.79 449439.61, 128473.49 449448.58, 128474.623 449448.963, 128474.775 449448.997, 128474.931 449449.016, 128475.087 449449.018, 128475.243 449449.005, 128475.396 449448.975, 128475.546 449448.93, 128475.69 449448.87, 128475.828 449448.795, 128475.957 449448.707, 128476.076 449448.606, 128476.185 449448.494, 128476.281 449448.371, 128476.365 449448.239, 128476.434 449448.099, 128476.489 449447.952, 128476.507 449447.884, 128482.689 449449.572, 128482.612 449450.178, 128482.773 449450.367, 128483.043 449450.519, 128483.6 449450.72, 128483.72 449450.76, 128538 449466.25, 128587.57 449480.08, 128634.44 449494.08, 128655.143 449500, 128670.6 449504.42, 128708.1 449515.01, 128748.61 449526.58, 128778.5 449535.25, 128831.15 449550.08, 128866.91 449560.22, 128867.21 449560.24, 128867.51 449560.2, 128867.79 449560.09, 128868.05 449559.94, 128868.27 449559.74, 128868.45 449559.5, 128868.58 449559.229, 128875.66 449561.255, 128875.65 449561.67, 128875.72 449562.08, 128875.87 449562.47, 128876.03 449562.73, 128876.35 449563.92, 128876.42 449565.18, 128876.22 449566.43, 128865.22 449597.56, 128865.18 449597.95, 128865.21 449598.34, 128865.32 449598.71, 128865.5 449599.05, 128865.74 449599.35, 128866.03 449599.6, 128866.37 449599.79, 128866.395 449599.797, 128865.961 449600.819, 128864.429 449604.427))

I have a problem with ST_Area function in MySQL 8.0.16. The result of the procedure call returns a different result than any library used to calculate the area of a polygon. Example: I have a GeoJSON with geometry like this:

{
        "type": "Polygon",
        "coordinates": [
            [
                [
                    16.91720977783202,
                    52.28469952907162
                ],
                [
                    16.910300407409657,
                    52.278608423688205
                ],
                [
                    16.919913444519032,
                    52.27753189128164
                ],
                [
                    16.91720977783202,
                    52.28469952907162
                ]
            ]
        ]
    }

The results of calulating area (in square meters) are: - Google Maps: 250165.80185391553 - Turf library: 250152.24833417358 - ST_Area(ST_GeomFromGeoJSON(@poly_json)): 202818.55768974536 First I was worried that GMaps (we got the coordinates from a map) is using some other projection, but it converts it into proper LatLng, so it shouldn't be a problem. Do you have an idea why the result is so different between calculating area on the database and on the client?

I have a table public.polygon_versions in my PostgreSQL 14.7 database:

CREATE TABLE public.polygon_versions (
    id bigint NOT NULL DEFAULT nextval('polygon_versions_id_seq'::regclass),
    entity_id bigint NOT NULL,
    creation_transaction_id bigint NOT NULL,
    obsolescence_transaction_id bigint,
    geo_json json NOT NULL,
    CONSTRAINT polygon_versions_pkey PRIMARY KEY (id),
    CONSTRAINT polygon_versions_creation_transaction_id_foreign FOREIGN KEY (creation_transaction_id)
        REFERENCES public.transactions (id),
    CONSTRAINT polygon_versions_entity_id_foreign FOREIGN KEY (entity_id)
        REFERENCES public.polygons (id),
    CONSTRAINT polygon_versions_obsolescence_transaction_id_foreign FOREIGN KEY (obsolescence_transaction_id)
        REFERENCES public.transactions (id)
);

Example geo_json data is: ~~~none {"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[30.3626584543099,50.43004834913466],[30.37021155489584,50.4281893337737],[30.36969657076498,50.433110097683176],[30.369009925257167,50.433984846616404],[30.3626584543099,50.43004834913466]]]},"properties":{}} ~~~ I'm trying to query all records where field geo_json matches some coordinates. For example: 30.504106925781265, 50.43731985018662 I've tried the following SQL query:

SELECT * 
FROM polygon_versions 
WHERE geo_json LIKE '%30.504106925781265,50.43731985018662%'

It returns an error:

ERROR:  operator does not exist: json ~~ unknown
LINE 1: SELECT * FROM polygon_versions WHERE geo_json LIKE '%30.5041...
                                                      ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 47

I have multiple tables with point geometries and a GiST index eating many gigabits of space. From what I understand about GiST indexes on polygons or polylines, it will store the bounding box and compute a first calculation on the bounding box instead of the full geometry resulting in faster processing. But what about point geometry? Will it do anything more than ordering the points spatially?

I want to suggest a few ideas to Oracle about enhancements to SDO_GEOMETRY / Oracle Spatial. Example: Requesting a function to select vertices, including the **multi-part numbers** of SDO_GEOMETRY polylines. ---------- **Question:** Do we have a way to ask Oracle for enhancements to SDO_GEOMETRY / Oracle Spatial? ----------- **What I tried:** I found the following page: How to Log an Idea (Enhancement Request) in the Customer Connect Idea Lab for Procurement Products (Doc ID 2577625.1) But unfortunately, my personal Oracle account isn't “connected” to a my organization's Oracle Support account (I'm not in IT; and my DBA doesn't want to submit ideas on my behalf). So I can't get past the "Connect your user account" step. ---------------- In a related question , it turned out that there was a way to request enhancements to a different product (SQL Developer) **right in the Oracle forum**. Which is great, because it's accessible to non-DBAs like me. So I was wondering if something similar might exist for Oracle Spatial. *And I'm not entirely sure if the "How to Log an Idea" page is the right avenue anyway. It seems like ideas might not get heard by the proper team...maybe there's a more direct way to talk to the Oracle Spatial team.*

I have MySQL 8 database with some tables that have some column of type GEOMETRY. When I back up the table with mysqldump, it outputs my geometry column as _binary '\æ\0\0
\... and when I try to restore the dump to fails with an error: "Cannot get GEOMETRY object from the data you send to the Geometry field". Any ideas how to resolve?

I'm working on a DB-spacial project in Mysql and I was trying to perform a union operation for a group of shapes. Im looking for something similar to the SQL Server function [UnionAggregate](https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/unionaggregate-geometry-data-type?view=sql-server-2017) , or the PostGIS Spatial Aggregate function [ST_Union](http://www.postgis.net/docs/ST_Union.html) for example (In SQL SERVER): SELECT geometry::STGeomFromWKB(Shape,27582), area_code FROM area_table WHERE area_code='xxxxxxx'; ST_GeomFromWKB do the same job in Mysql. in the second part and with the UnionAggregate function + group by : select dbo.UnionAggregate((geometry::STGeomFromWKB(Shape,27582)).MakeValid()), area_code FROM area_table WHERE area_code='xxxxxxx' GROUP BY area_code; How to perform a similar operation (aggregate+group by) in MySQL?

I am trying to run a mysql query below: select * from geo where st_distance_sphere(Point(-118.2842557, 34.0676834), geometry) <= 1609 where geometry is of type Polygon. and get an error: Error Code: 3548. There's no spatial reference system with SRID 1498173264. MySQL Engine version 8.0.27 My table structure is like: CREATE TABLE geo (id int, geometry text, spatialdata GEOMETRY); INSERT INTO geo (id, geometry) VALUES (2, 'POLYGON ((-118.282067 34.067679, -118.281555 34.067458, -118.281467 34.067419, -118.280624 34.067055, -118.279672 34.066689, -118.279376 34.066558, -118.279233 34.066489, -118.279098 34.066409, -118.278879 34.066251, -118.278047 34.065631, -118.276573 34.067943, -118.276505 34.06805, -118.275848 34.069081, -118.275294 34.069984, -118.274929 34.070554, -118.273876 34.072198, -118.27485 34.072519, -118.2753 34.072711, -118.275707 34.072889, -118.276615 34.073286, -118.27752 34.073681, -118.277735 34.073343, -118.277791 34.073256, -118.278056 34.072844, -118.278522 34.072117, -118.278988 34.071389, -118.279463 34.070648, -118.280081 34.070915, -118.280249 34.070772, -118.281158 34.06934, -118.282183 34.06773, -118.282067 34.067679))') (3, 'POLYGON ((-118.555285 34.362201, -118.555219 34.361998, -118.554778 34.360634, -118.554424 34.359603, -118.553965 34.358659, -118.553921 34.358567, -118.553439 34.357795, -118.552339 34.356615, -118.55166 34.356, -118.551612 34.355999, -118.551194 34.355994, -118.549745 34.354685, -118.548745 34.353768, -118.547909 34.353045, -118.547195 34.352502, -118.546862 34.3523, -118.545641 34.351656, -118.543184 34.350706, -118.542851 34.350577, -118.542381 34.350396, -118.542073 34.350288, -118.542008 34.350252, -118.539649 34.349332, -118.53605 34.347959, -118.535769 34.34785, -118.535125 34.347599, -118.534338 34.347228, -118.533434 34.346728, -118.532705 34.346214, -118.532041 34.345665, -118.531035 34.344554, -118.530628 34.344145, -118.530238 34.343806, -118.529486 34.343184, -118.528729 34.342756, -118.528053 34.342392, -118.527135 34.342008, -118.526355 34.341728, -118.52598 34.341644, -118.524945 34.341434, -118.523048 34.341227, -118.522788 34.341195, -118.521972 34.341063, -118.520987 34.340846, -118.52024 34.340662, -118.519549 34.340425, -118.518682 34.340007, -118.517922 34.33964, -118.517862 34.339612, -118.517141 34.339279, -118.516271 34.338841, -118.514968 34.33821, -118.514134 34.337806, -118.513566 34.337526, -118.512981 34.337237, -118.512383 34.336832, -118.512047 34.336584, -118.511511 34.33623, -118.511235 34.336052, -118.510177 34.335397, -118.509312 34.33482, -118.509297 34.334807, -118.508874 34.334455, -118.508688 34.334258, -118.508467 34.334022, -118.508341 34.33389, -118.508001 34.334146, -118.507788 34.334306, -118.507766 34.334322, -118.507725 34.334368, -118.507716 34.334377, -118.507672 34.334405, -118.507629 34.334435, -118.507525 34.334504, -118.507444 34.334564, -118.507364 34.334625, -118.507274 34.334693, -118.50723 34.334726, -118.507098 34.334825, -118.506869 34.334998, -118.506832 34.335026, -118.506637 34.335173, -118.506612 34.335191, -118.506514 34.335262, -118.506455 34.335308, -118.506396 34.335355, -118.506346 34.335393, -118.506004 34.335651, -118.505766 34.335831, -118.504007 34.337157, -118.50408 34.337281, -118.504095 34.337306, -118.504293 34.337511, -118.504534 34.337705, -118.504802 34.337886, -118.505113 34.338061, -118.50549 34.338208, -118.50577 34.338306, -118.506451 34.338544, -118.50662 34.338603, -118.506795 34.338661, -118.506944 34.338711, -118.507221 34.338802, -118.507541 34.338908, -118.507584 34.338922, -118.507655 34.338946, -118.508014 34.339116, -118.508238 34.339267, -118.50835 34.339342, -118.508671 34.33959, -118.508848 34.339785, -118.508998 34.340005, -118.509112 34.340239, -118.50921 34.340505, -118.50941 34.341288, -118.509444 34.341422, -118.509452 34.341455, -118.50973 34.342602, -118.509865 34.343126, -118.510004 34.343667, -118.510189 34.344455, -118.510262 34.34481, -118.510276 34.34504, -118.51026 34.345371, -118.510211 34.345662, -118.510098 34.346009, -118.509945 34.346321, -118.509799 34.346554, -118.509298 34.34722, -118.509253 34.347281, -118.508975 34.34766, -118.508934 34.347715, -118.508673 34.348174, -118.508438 34.348649, -118.508233 34.349267, -118.508156 34.349578, -118.508118 34.349733, -118.50806 34.350197, -118.50806 34.350747, -118.508099 34.351257, -118.508288 34.352505, -118.508287 34.353582, -118.508201 34.354367, -118.507916 34.356055, -118.507822 34.356613, -118.507796 34.356747, -118.507702 34.357234, -118.50762 34.357593, -118.507411 34.358367, -118.507276 34.358785, -118.507189 34.358874, -118.507008 34.359371, -118.506958 34.359648, -118.506924 34.359985, -118.506928 34.360195, -118.506964 34.360888, -118.506977 34.361142, -118.507002 34.361509, -118.507007 34.361581, -118.507097 34.361847, -118.507132 34.362394, -118.507133 34.362472, -118.507135 34.362584, -118.50714 34.362752, -118.507146 34.362964, -118.507073 34.36319, -118.507053 34.363521, -118.507029 34.363792, -118.506999 34.363962, -118.506927 34.364264, -118.506852 34.364572, -118.506794 34.364785, -118.506782 34.364968, -118.506705 34.365148, -118.506528 34.365481, -118.506325 34.365771, -118.506268 34.365851, -118.506795 34.366019, -118.507274 34.366207, -118.507576 34.366365, -118.507943 34.366558, -118.508006 34.366596, -118.508271 34.366758, -118.508796 34.367078, -118.508906 34.367147, -118.509405 34.367455, -118.509957 34.367795, -118.510023 34.367835, -118.510495 34.368121, -118.510658 34.368226, -118.510686 34.368244, -118.510906 34.368385, -118.511282 34.368627, -118.511373 34.368685, -118.511539 34.368788, -118.512044 34.369102, -118.512099 34.369137, -118.512221 34.369213, -118.512529 34.3694, -118.513119 34.369759, -118.51331 34.369879, -118.514054 34.370345, -118.514073 34.370356, -118.514201 34.370431, -118.514405 34.370557, -118.514744 34.370766, -118.514942 34.370887, -118.515141 34.371011, -118.515721 34.371375, -118.516361 34.371762, -118.516555 34.371885, -118.516796 34.372031, -118.517262 34.372313, -118.517487 34.37245, -118.51772 34.372594, -118.517957 34.372742, -118.518128 34.372848, -118.518184 34.372881, -118.518582 34.373121, -118.518847 34.373239, -118.518881 34.373254, -118.519202 34.373396, -118.519553 34.373552, -118.520225 34.373845, -118.520735 34.374067, -118.521184 34.374259, -118.521522 34.374403, -118.521871 34.374553, -118.521996 34.374607, -118.522891 34.374994, -118.523268 34.375161, -118.523397 34.375218, -118.523683 34.375338, -118.523938 34.375445, -118.524107 34.375515, -118.524534 34.375691, -118.524708 34.375763, -118.525182 34.375979, -118.525717 34.376223, -118.525988 34.376392, -118.526089 34.376472, -118.526295 34.376497, -118.526425 34.376527, -118.52666 34.376626, -118.526869 34.376681, -118.527547 34.376969, -118.52777 34.377063, -118.528044 34.37718, -118.529011 34.377589, -118.529427 34.377766, -118.529979 34.378014, -118.530416 34.37821, -118.530488 34.378241, -118.530966 34.378445, -118.53115 34.378524, -118.531275 34.378579, -118.531646 34.378743, -118.531935 34.378868, -118.532104 34.379027, -118.532158 34.379094, -118.532357 34.379368, -118.532552 34.379644, -118.532768 34.37995, -118.53279 34.379981, -118.532949 34.380186, -118.53316 34.380163, -118.53392 34.380114, -118.534117 34.380105, -118.534723 34.380077, -118.534871 34.38007, -118.535799 34.38001, -118.536569 34.379962, -118.537387 34.379909, -118.537611 34.379894, -118.538054 34.379867, -118.538373 34.379848, -118.538877 34.37982, -118.539027 34.379812, -118.539138 34.379804, -118.539239 34.379797, -118.539475 34.379779, -118.539811 34.379754, -118.540602 34.379708, -118.540775 34.379698, -118.540878 34.379692, -118.541868 34.379625, -118.542443 34.379595, -118.542935 34.37957, -118.543308 34.379549, -118.543654 34.379529, -118.544072 34.379506, -118.54425 34.379495, -118.545853 34.379401, -118.545999 34.379392, -118.546854 34.379343, -118.546937 34.379338, -118.546908 34.378931, -118.546899 34.37881, -118.546863 34.378387, -118.546818 34.377853, -118.546808 34.377745, -118.54672 34.376693, -118.546441 34.376683, -118.545898 34.376701, -118.545604 34.37671, -118.544716 34.376767, -118.543927 34.376816, -118.543348 34.376852, -118.54303 34.376871, -118.542202 34.376923, -118.542159 34.376414, -118.542123 34.376003, -118.542102 34.375756, -118.542072 34.375405, -118.542064 34.375313, -118.542037 34.374991, -118.54202 34.374748, -118.541918 34.373244, -118.541903 34.373018, -118.541882 34.372882, -118.541839 34.372597, -118.541828 34.372474, -118.541793 34.372086, -118.541772 34.371818, -118.541768 34.371779, -118.541737 34.371384, -118.541717 34.371136, -118.541698 34.370912, -118.541683 34.370712, -118.541627 34.37, -118.541586 34.369472, -118.541216 34.36949, -118.540862 34.369516, -118.54053 34.369539, -118.539582 34.369607, -118.539536 34.369252, -118.539514 34.369081, -118.539416 34.368151, -118.539402 34.368015, -118.539376 34.36767, -118.539325 34.366997, -118.539411 34.36638, -118.539512 34.366032, -118.539652 34.365769, -118.539888 34.365403, -118.540355 34.364918, -118.540463 34.36483, -118.540978 34.364411, -118.541279 34.364274, -118.541541 34.364154, -118.542173 34.363957, -118.542619 34.363867, -118.543167 34.36384, -118.543837 34.363827, -118.544191 34.363809, -118.544366 34.363801, -118.544637 34.363749, -118.545047 34.363635, -118.545407 34.363523, -118.54568 34.363438, -118.546353 34.363341, -118.546751 34.363336, -118.54717 34.363379, -118.547452 34.363447, -118.547804 34.36354, -118.548195 34.363684, -118.548368 34.363775, -118.548788 34.363998, -118.549112 34.364163, -118.549475 34.364348, -118.54973 34.364436, -118.550081 34.364558, -118.55067 34.364655, -118.551088 34.364666, -118.551612 34.364639, -118.552072 34.364557, -118.552728 34.364315, -118.552845 34.36423, -118.553457 34.36379, -118.553665 34.36364, -118.554027 34.36338, -118.554169 34.363247, -118.554228 34.363192, -118.554695 34.362754, -118.555039 34.362431, -118.555241 34.362243, -118.555285 34.362201))') https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=12ba2b0bfcb6e04fee7361cb85496569 What SRID should I use? and how do I set it in MySQL? I am not explicitly setting SRID. When I try to set - ALTER TABLE geo ADD geom Polygon; UPDATE geo SET geom = Polygon(geometry) I get an error, Error Code: 1367. Illegal non geometric 'geo' value found during parsing

I have a table x like this: Columns: SubjectID int(11) Timestamp bigint(20) fix_geom point When i try to group my fix_geom column to a MultiPoint (or GeometryCollection) by SubjectID, like: SELECT SubjectID, ST_AsText(MultiPoint(fix_geom)) FROM x WHERE SubjectID = 100 GROUP BY SubjectID; i get a table with a multipoint geometry, but it contains just one single Point instead of the many i'd liked to see there...: Can anybody tell me what i'm doing wrong? Cheers and many thanks in advance, Olaf

I am trying the following insert query on mysql 8. Insert Into fence Set fenceName='aa', radius=2, fenceGeometry=ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326) It works perfectly on mysql 5.7 but here in mysql 8 I get this error Latitude 102.189331 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]. Based on some suggestion I also did this. ALTER TABLE fence MODIFY fenceGeometry geometry NOT NULL SRID 4326; But yet the it gives me the same results.

*Oracle 18c:* In a related post, I demonstrated how to use the **GetVertices()** function and the **Table()** function to extract the vertices of an SDO_GEOMETRY line: Select SDO_GEOMETRY line vertices as rows select a.line_id, b.id as vertex_id, b.x, b.y from a_sdo_geometry_tbl a, table(sdo_util.getvertices(a.shape)) b -- SDO_UTIL.GETVERTICES > > This function returns an object of MDSYS.VERTEX_SET_TYPE, which > consists of a table of objects of MDSYS.VERTEX_TYPE. Oracle Spatial > and Graph defines the type VERTEX_SET_TYPE as: > > CREATE TYPE vertex_set_type as TABLE OF vertex_type; > > Oracle Spatial and Graph defines the object type VERTEX_TYPE as: > > CREATE TYPE vertex_type AS OBJECT > (x NUMBER, > y NUMBER, > z NUMBER, > w NUMBER, > v5 NUMBER, > v6 NUMBER, > v7 NUMBER, > v8 NUMBER, > v9 NUMBER, > v10 NUMBER, > v11 NUMBER, > id NUMBER); > > Note: The VERTEX_SET_TYPE and VERTEX_TYPE types are intended for use by > Oracle only. Do not use these types in column definitions or functions > that you create. --------------------------- Question: Is there a way to extract an SDO_GEOMETRY line’s vertices into a nested table column in a query -- without using the GetVertices() function? The output would be a query resultset, not a static table. -------------- Hints: The following functions might be useful: PointN: Returns a point that is the nth vertex in the collection of vertices GetNumVertices: Returns the number of vertices in the input geometry.

I have an Oracle 18c table that has an SDO_GEOMETRY column (lines): create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry); insert into a_sdo_geometry_tbl (line_id, shape) values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754)) ); insert into a_sdo_geometry_tbl (line_id, shape) values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658)) ) ---------------- select line_id, sdo_util.to_wktgeometry(shape) as well_known_text from a_sdo_geometry_tbl; LINE_ID WELL_KNOWN_TEXT -------------------------------------------------------------------------------- 1 LINESTRING (671539.685273438 4863324.18143614, 671595.050070336 4863343.16655619, 671614.013553706 4863350.34348304, 671622.204415338 4863353.52539613) 2 LINESTRING (71534.5567096211 4863119.99180975, 671640.738468866 4863157.13274525, 671684.86211504 4863172.02299559) 3 LINESTRING (671622.204415338 4863353.52539613, 671633.326716411 4863357.84622911, 671904.061407769 4863451.28616675) 4 LINESTRING (671684.862052112 4863172.02299559, 671892.149614432 4863244.14144007, 671951.21565712 4863264.82431039, 671957.447146119 4863266.84761768, 671966.824385692 4863269.14663266) 4 rows selected. -------------------------- For each line, I want to select each vertex as a separate row in a query/resultset. How can I do this?

It doesn't seem to be a WKB, since it's basically a string, so what is it?

I have a table containing a geometry column in 4236 projection. I take the ST_X and ST_Y values from one row, and create a point from them. I then transform the point and the original geometry column value into 3857 projection to get a 2d distance between then using ST_DISTANCE. I expect the distance to be ~0. The answer I actually get it 1216.something metres. Here is example code which creates two points, which should be identical, and takes the distance between them, giving the same ~1216m answer select ST_Distance( ST_Transform( ST_SetSRID( ST_MakePoint(5.6100, 58.8900), 4236), 3857), ST_Transform( ST_SetSRID( ST_MakePoint(5.6100, 58.8900), 4326), 3857)); I'm presumably making some stupid mistake here (I am by no means a GIS expect), but what is it that I'm doing wrong?