Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [geometry]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
12 views

Correct way of storing the "reach profile" of an object

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 ...
Ambidex's user avatar
  • 111
0 votes
1 answer
48 views

Slow join on geometry fields

My situation is somewhat similar to this earlier question: In PostgreSQL I have one table containing point geometries (P), and another containing polygons (G). I would like to join on the geometry ...
Berend's user avatar
  • 121
0 votes
2 answers
123 views

Find rows with given coordinates nested in a JSON column

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 ...
Taras's user avatar
  • 157
0 votes
1 answer
73 views

Improving the performance of STEquals

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) and a 'geography' (WGS84) version of the ...
R. Hoek's user avatar
  • 119
0 votes
0 answers
271 views

Mysqldump geometry field error

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 ...
adam78's user avatar
  • 155
0 votes
1 answer
1k views

Error Code: 3548. There's no spatial reference system with SRID 1498173264 - 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 ...
kms's user avatar
  • 129
0 votes
1 answer
271 views

storing altitude/height with MySQL geometry

We are using MySQL geometry data type to store polygons, polylines, multi-polygons and multi-polylines. We have come across the need to store depth/height data along with some of these features. My ...
mike16889's user avatar
  • 209
0 votes
1 answer
44 views

How to request an enhancement to SDO_GEOMETRY / Oracle Spatial

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 ...
User1974's user avatar
  • 1,467
0 votes
2 answers
155 views

Select SDO_GEOMETRY line vertices as rows -- using recursive WITH clause

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) ...
User1974's user avatar
  • 1,467
0 votes
1 answer
590 views

Extract SDO_GEOMETRY line vertices into a nested table column (in a query resultset)

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 ...
User1974's user avatar
  • 1,467
0 votes
1 answer
143 views

Select SDO_GEOMETRY line vertices as rows

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) ...
User1974's user avatar
  • 1,467
2 votes
3 answers
570 views

Is a GIST index on a geometry point useful to speed up a spatial query?

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 ...
Boodoo's user avatar
  • 45
1 vote
1 answer
515 views

What is the format of raw geometry column in PostGIS?

It doesn't seem to be a WKB, since it's basically a string, so what is it?
jayarjo's user avatar
  • 159
0 votes
4 answers
59 views

Postgis reporting a point is 1216m away from itself using ST_Distance [closed]

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 ...
khafka's user avatar
  • 79
0 votes
1 answer
323 views

MariaDB ERROR 1048 GeomFromText returning as null

I am trying to restore a databese from an old server onto a new server and my geomertry columns are not importing example UPDATE `property_floor` SET `floorBounds` = ST_GeomFromText('MULTIPOINT((115....
mike16889's user avatar
  • 209
1 vote
1 answer
155 views

GeoJSON MultiLineString import to MySQL with SRID/CRS 27700, 'out of range'

Version 8.0.20 I'm importing a single link of the OSM road network with the intention of creating a buffer in MySQL. This is it: select st_srid(st_geomfromgeojson('{ "type": "Feature&...
Andy H.'s user avatar
  • 13
1 vote
2 answers
2k views

Geo Route - Best way/db to store route path

We are developing an app in that UI need to show a path where a truck driver drives the truck. Driver mobile will continuously send (example every 10 seconds) their live location. Which database is ...
Ankur Loriya's user avatar
0 votes
1 answer
370 views

Trigger to enforce unique constraint on text column (Geometry) to resolve INSERT conflicts

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 ...
Chintan Pathak's user avatar
0 votes
2 answers
2k views

How to get the type of geometry column in postgres?

I need to determine the type of geometry column and doing it with the following query: SELECT type FROM geometry_columns WHERE f_table_schema = 'public' AND f_table_name = 'table_name' ...
jayarjo's user avatar
  • 159
0 votes
0 answers
319 views

Can't get PostGIS to show points on map - no button to activate map?

PostgreSQL 12 and PostGIS 3.0.1 on Windows 10, using PGAdmin4. I'm totally new to PGAdmin4 having recently come from PostgreSQL 9 / PGAdmin III - frankly, I don't like it but I'm trying. For the ...
Stuart K. Smith's user avatar
3 votes
1 answer
1k views

How to add Spatial Reference ID 28992 to MSSQL server?

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server? My end goal is to get this dataset into MS SQL Server, with the Geography Markup ...
Jens Wagemaker's user avatar
2 votes
0 answers
548 views

MySQL 8 - Optimize query using ST_Contains or ST_Within

I have a table called locations with ~70000 records which includes a geometry column of points and a spatial index as follows: UPDATE locations SET geom = ST_GeomFromText(CONCAT( 'POINT(', latitude, '...
adam78's user avatar
  • 155
1 vote
1 answer
163 views

MySQL 8.0.18 - ST_Area returns invalid value

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 ...
arkus's user avatar
  • 111
0 votes
1 answer
4k views

Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?

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 ...
khafka's user avatar
  • 79
2 votes
1 answer
154 views

Using PHP form to insert geometry from WKT and SRID into SQL Server 2017

I have a PHP 7.3 form that asks the user for a Name, a WKT and an SRID. I would like to upload the WKT and SRID to SQL Server 2017 so that it creates a geometric object in Upload_WKT_Test using ...
Rayner's user avatar
  • 121
3 votes
1 answer
1k views

How to formulate equality predicate on POINT column in PostgreSQL?

How to select from a table with an equality predicate on point column? To be clear: it's a native Postgres point data type, not geometry(point) from PostGIS. select a.* from indsolv.address As a ...
Thirumal's user avatar
  • 2,448
1 vote
1 answer
1k views

How to create Polygon from Point datatype in MySQL?

I have a table like this: CREATE TABLE `aois` ( `aois_id` int(11) NOT NULL DEFAULT '0', `WS_A` point DEFAULT NULL, `WS_B` point DEFAULT NULL, `WS_C` point DEFAULT NULL, `WS_D` point DEFAULT ...
Olaf Briese's user avatar
10 votes
2 answers
6k views

Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]

I am trying the following insert query on mysql 8. Insert Into fence Set fenceName='aa', radius=2, fenceGeometry=ST_GeomFromText('POINT(...
newbie's user avatar
  • 207
0 votes
1 answer
72 views

How to run multiple ST_DWITHINs/ ST_INTERSECTS within a single WHERE clause

PostgreSQL 11, on Windows 10. I'm working with a post-gis enabled database. I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the geometry of ...
Theo F's user avatar
  • 151
1 vote
2 answers
1k views

How to group-by to get MultiPoint/GeometryCollection in MySQL?

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: ...
Olaf Briese's user avatar
1 vote
2 answers
922 views

Testing Geometry Polygon Data is Valid Using a Stored Procedure

We are using a SQL Server Spatial database with the following table setup: SRID of 27700 geometry_column of ogr_geometry geometry_type of POLYGON I am using a SQL Server stored procedure to perform ...
Mike Poole's user avatar
0 votes
1 answer
145 views

SQL syntax issue with geometry

I must be missing something pretty basic, but i have 2 tables. Both are basic, just a primary key (as a uniqueidentifier) and a geometry column in each. I'm trying to find if a point in one of the ...
Phil's user avatar
  • 287
7 votes
2 answers
602 views

How to setup solution for multi-dimensional distance search with SQL Server spatial data types

I have an existing database solution that is not performing very well. I am searching on multi-dimensional data with a traditional table structure using floats, etc. The database contains ~ 1-2M rows. ...
wilkokosten's user avatar
0 votes
1 answer
54 views

Basic Geometry Representation

I have an assignment in my introduction to database course. I chose to try and do a database model to represent basic geometric figures and mathematical objects (functions, polynomes, etc). For ...
Carlos Miranda's user avatar
1 vote
1 answer
892 views

How do I dump spatial types, like point, with their SRIDs in MySQL?

I am testing the point data type on Mysq 5.7 below is my java insert query. String insertQuery1 ="INSERT INTO gtest3 SET " + "gDateTime='2017-07-16 01:00:00'"+ ",vehicleID=1"+ ",...
user8012596's user avatar
1 vote
1 answer
550 views

COM error on UnionAggregate

Has anyone ever come across a COMException while working with geography data in SQL Server? I didn't notice the error until I ran .Reduce() on the original zip code data. It may have happened before ...
Julian Goldsmith's user avatar
0 votes
2 answers
1k views

Converting spatial data from Oracle to MySQL

This is a followup to this question: Problem getting zips in raius via MySQL I have some spatial stuff in an Oracle table. MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),...
I wrestled a bear once.'s user avatar
9 votes
4 answers
1k views

Problem getting zips in radius via MySQL

I have a table of zip codes which includes the center lat, lng for each zip code. I use it to get a list of zip codes within a given mile radius from any arbitrary point. It just occurred to me that, ...
I wrestled a bear once.'s user avatar
4 votes
1 answer
1k views

MySQL equivalent of a spatial ST_Union aggregate function?

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, or the ...
Yassine LD's user avatar
2 votes
1 answer
362 views

Is there a way other than WKT to insert geometry to SQL Server?

I am using design data from *.dwg and *.dgn to insert geometries to SQL Server field which is set to geometry. All examples I see are using something called WKT which is a specification by a company ...
user7387653's user avatar
0 votes
2 answers
1k views

Store Mesh in database

I hope this is the correct forum but will ask anyway: I have lots of Mesh data I want to store. Mesh is actually triangles. I hope it's not too of beginner question but wouldn't Geometry type of SQL ...
user114472's user avatar
1 vote
1 answer
988 views

SDO_GEOM.SDO_INTERSECTION between a line and a polygon returns what?

I'm confused as to what exactly the SDO_GEOM.SDO_INTERSECTION returns in Oracle. Documentation is here, but only gives examples of two intersecting polygons. What if I have many lines, some of them ...
Sam's user avatar
  • 111
2 votes
1 answer
199 views

Seaching points within a radius - performance issues

i'm in trouble and completely lost with a geometry search, using 2 tables, linked by uid, each one contains about 5 millions rows. One table with address and point coordinates, Second with user status ...
Zoé R.'s user avatar
  • 61
8 votes
3 answers
890 views

Cross join on a numbers table to get line vertices, is there a better way?

The Question: I have a spatial table (road lines), stored using ESRI's SDE.ST_GEOMETRY user-defined datatype in an Oracle 12c geodatabase. I want to list the line vertices so that I can ultimately ...
User1974's user avatar
  • 1,467
2 votes
1 answer
479 views

Why I can't calculate the distance between a fixed point and a geometry field of a MySql table using this distance function? [closed]

I am not so into database and GIS and I have the following problem. I have defined this function that return the distance between 2 points in the space represented by 2 point fields: CREATE FUNCTION ...
AndreaNobili's user avatar
0 votes
3 answers
3k views

ORDERBY timestamp performance issue

I have some queries dealing with coordinates stored in POINT. Everything is ok without order (about 0.6 ms to query a 5 millions posts and 1 million users database, for testing) but go to 13s when add ...
Zoé R.'s user avatar
  • 61
0 votes
2 answers
357 views

Update all geometry points by adding/subtracting from XY

I want to update the POINT geometry column for all rows in a table. For example, I want to change POINT(1 1) to POINT(1.1 .9) UPDATE myTable SET geoPoint = asText(geoPointX+1 geoPoint Y-1) The ...
Caleb Pitman's user avatar
1 vote
1 answer
739 views

How do I find the cumulative distance between geometry points in SQL Server 2014? [closed]

I have a database designed to be a proof of concept for delivery of products from a central depot to customers distributed at random locations within ten miles of the depot. The depot location is ...
James's user avatar
  • 19
2 votes
1 answer
1k views

What is the point of a SRID in a geometry type?

From my understanding, SRID is a projection code; how the 2d coordinate system is fit over a spheroid. The difference between Geometry and Geography types, from my understanding, is that a geometry ...
Justin Dearing's user avatar
1 vote
1 answer
2k views

What is the fastest way to compare polygon geometry data?

We have two tables with polygons stored in geometry type columns. I want to fetch the polygons present in one table that are not present in another table. As of now I am doing a left outer join ...
Balu's user avatar
  • 71