Questions tagged [geometry]
The geometry tag has no usage guidance.
49
questions
0
votes
1
answer
28
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 ...
- 101
0
votes
2
answers
114
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 ...
- 147
0
votes
1
answer
47
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 ...
- 119
0
votes
0
answers
148
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 ...
- 155
0
votes
0
answers
95
views
SQL Server: Spatial Indexing and Bounding Box
I have two tables each with a SHAPE Geom Column, and I'm working on getting the intersected area between every shape in the first table with the second table.
The second table has millions of rows, ...
- 101
0
votes
1
answer
630
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 ...
- 129
0
votes
1
answer
177
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 ...
- 209
0
votes
1
answer
42
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 ...
- 1,437
0
votes
2
answers
131
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)
...
- 1,437
0
votes
1
answer
431
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
...
- 1,437
0
votes
1
answer
111
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)
...
- 1,437
2
votes
3
answers
279
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 ...
- 45
1
vote
1
answer
414
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?
- 149
0
votes
4
answers
56
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 ...
- 69
0
votes
1
answer
257
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....
- 209
1
vote
1
answer
137
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&...
- 13
1
vote
2
answers
1k
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 ...
- 125
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'
...
- 149
0
votes
0
answers
261
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 ...
3
votes
1
answer
950
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 ...
- 131
2
votes
0
answers
474
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, '...
- 155
1
vote
1
answer
144
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 ...
- 111
0
votes
1
answer
3k
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 ...
- 69
2
votes
1
answer
151
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 ...
- 121
2
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 ...
- 2,298
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 ...
- 33
9
votes
2
answers
4k
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(...
- 197
0
votes
1
answer
55
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 ...
- 141
1
vote
2
answers
896
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:
...
- 33
1
vote
2
answers
750
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 ...
- 111
0
votes
1
answer
134
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 ...
- 267
7
votes
2
answers
559
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.
...
- 171
0
votes
1
answer
49
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 ...
1
vote
1
answer
845
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"+
",...
- 227
1
vote
1
answer
515
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 ...
- 171
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),...
9
votes
4
answers
967
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, ...
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 ...
- 798
2
votes
1
answer
338
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 ...
- 25
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 ...
1
vote
1
answer
825
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 ...
- 111
2
votes
1
answer
179
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 ...
- 61
8
votes
3
answers
869
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 ...
- 1,437
2
votes
1
answer
470
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 ...
- 123
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 ...
- 61
0
votes
2
answers
335
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 ...
- 217
1
vote
1
answer
729
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 ...
- 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 ...
- 2,677
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 ...
- 71