Questions tagged [geography-data-type]
geography data type, stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.
108
questions
2
votes
1
answer
63
views
Create a new point geometry from origin point geometry, distance and bearing in PostgreSQL
I am trying to create a new point geometry field from this command:
UPDATE cat.datos_parcelas
SET arb_geom = ST_SetSRID(ST_Project(geom, arbdist, radians(arbrumbo)), 32631)::geometry;
-- geom: ...
- 165
0
votes
1
answer
112
views
Geography type vs Geometry type for PostGIS intersections and other calculations
According to ChatGPT (which obviously could be very wrong) geometry type will always calculate based on Cartesian space even when the CRS is 4326 coordinates, whereas geography type will calculate ...
- 584
1
vote
0
answers
56
views
ST_intersects returns different result for Geometry params vs Geography params
ST_Intersects returns true for geography parameters but it returns false for geometry params:
Following is the query for geometry params and it returns true which is incorrect.
select ST_Intersects (...
- 53
2
votes
0
answers
111
views
Geometry comparisons in SQL
Newbie to GIS here.
Equality can be defined on a geographic value by seeing if they have a collection of the same points. For example, something like:
-- The equality
-- `LINESTRING(1 1, 2 2) = ...
- 131
3
votes
1
answer
64
views
PostGIS ST_Relate reports polygons intersect but Google Maps says otherwise
I'm creating a Geography database that does not allow polygons to overlap/intersect (having positive common area that is). The user interaction flow is:
User creates and exports a KML document from ...
- 31
2
votes
0
answers
102
views
Converting Geometry to a Geography yields an invalid shape SQL SERVER
I have two Geometry shapes of types Polygon or MultiPolygon [with Lat & Lng points] that I want to get their intersected shape.
I do this as follows:
land_shape.STIntersection(grid_shape) as shape
...
- 151
0
votes
0
answers
19
views
Tree Cover Density raster layer: is it considered a categorical raster?
I am using the Tree Cover Density (TCD) raster layer from EEA. The raster has integer values from 0-255, where each integer represents the percentage of tree cover density.
I want to use a machine ...
- 91
1
vote
1
answer
49
views
ST_Covers(envelope, point) yields different results for same point
Clearly missing a basic concept. Using ST_Covers, I'm getting "true" for a geography point that clearly exists outside of the polygon. Looking at a number of points, it appears latitudes are ...
- 121
0
votes
1
answer
146
views
Google Earth Engine update band values of aggregated image to just the values depicted in brackets in the band when data type is double
I am trying to aggregate the hansen forest loss data from a 30 x 30 m resoltuion to 1 km x 1 km resolution, where the value of the band is the proportion of the cell coverage. I feel like I am close ...
- 3
2
votes
1
answer
113
views
geometry vs geography for distance measurement and large dataset
I'm working on a project where I have clusters of GPS coordinates to store, let's say ~20k points per cluster. The clusters overlay with big cities, like London or New York. I have many clusters ...
- 143
2
votes
1
answer
353
views
converting geometry of SRID 0 to geography
My geometry columns have SRID of 0. Can I simply convert them to geography as such:
routes.geom::geography <-> pc.geom::geography as distance
in order to get "distance" in metres?
P.S -...
- 839
1
vote
0
answers
70
views
Unexpected ST_DWithin result when using geography [closed]
This query:
SELECT ST_DWithin(
ST_Buffer('SRID=4326;LINESTRING(7.1670005 45.9968257,7.1670216 45.9969537,7.1670216 45.9971326, 7.1670484 45.9972789, 7.1671905 45.9974074, 7.1672844 45.9974801, 7....
- 111
3
votes
0
answers
259
views
Efficient way to "gridify" SQLGeography shape
Read the "Background" below for full details of why I want to achieve this.
Given a shape, I would like to process it in such a way that it conforms to a 0.00001 x 0.00001 degree grid. (This ...
- 31
1
vote
1
answer
2k
views
SQL Server STIntersects, STDistance and STCointains with Lat Lon points and Geometry Polygons [closed]
I need to create 3 functions in SQL Server (2017) that has to do the following:
IsPointInsideTown(Point, TownCode, [SRID]) -> true/false: indicates if the point is inside the town's polygon.
...
- 329
6
votes
1
answer
273
views
ogr2ogr cannot import into table with two geography columns
I am using ogr2ogr to import geojson files into a PostGIS table. The table has two geography columns ("footprint" and "centroid"). The geojson files contain polygon features, and I ...
- 1,294
2
votes
1
answer
187
views
Make geographic squares that follow parallels in SQL Server
I have a list of points (with latitude and longitude) in an SQL Server database, and for each of these points I have to generate a 50 x 50 degrees square (the point being the South West angle of the ...
- 131
1
vote
0
answers
640
views
SQL Server error when creating Geography data from a Multipolygon Geometry
So, in SQL server, I have a table called Municipios, which contains a column with 2463 entries of geometry type, with their SRID set to 4326.
I'm running the following Query to try to convert them to ...
- 41
1
vote
1
answer
393
views
PostGIS-Geography doesn't show in QGIS after database restore
I recently restored a PostgreSQL database from a SQL dump. It worked well insofar as all my tables and data are restored. However, one of the original tables contained a PostGIS geography column ...
- 1,598
1
vote
1
answer
367
views
Assurance that ST_Envelope works with geography as expected
Using PostGIS, I have a table of data with geographic points defined as last_known_coordinates GEOGRAPHY(POINT,4326). I want to find all of these within a geographic bounding box with given top left ...
- 113
4
votes
1
answer
803
views
Inserting geography data types with GeoPandas
I would like to insert a geodataframe containing point geometries in SRID 4326 to an existing table in PostGIS+PostgreSQL. The issue is that I would like to retain the existing table structure, which ...
- 2,072
3
votes
0
answers
222
views
BRIN Index on PostGIS Geography
I have about 1.5 billion points in a geography column which represent topography.
I'll be retrieving all the points that are within a certain distance of a given long/lat. I was able to create a GIST ...
1
vote
0
answers
149
views
SQLServer STArea issue with GEOMETRYCOLLECTION resulting in trillion's m2 area
I've come across a weird issue with SQL Server's handling of GEOMETRYCOLLECTION in a Geography data type resulting in an area (square meters) in the trillion's of square meters. (checked on 2014, ...
- 11
0
votes
0
answers
111
views
PostGIS Geography Data Changed After pg_restore
Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using a Docker image kartoza/postgis:13.1.
A dump of the database was ...
- 1,667
3
votes
1
answer
403
views
Geography in GeoPandas?
Is there a way to make GeoPandas work with geographies?
If GeoPandas were used with pygeos, is there a way to force all calculations (ST_Distance, ST_Length, etc.) to be geodesic? How do I tell it ...
- 39
1
vote
1
answer
566
views
How to get most accurate Point-Point distance queries in Europe with Spatialite
I have a GeoDjango app, currently on PostGIS, which I am converting to use Spatialite db instead. I have no specialised knowledge about GIS systems :(
The locations table has a Geography column ...
- 123
11
votes
1
answer
4k
views
Is the Geometry(4326) an alias for Geography datatype?
The main reference about Geography datatype seems docs/using_postgis_dbmanagement:
Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat (SRID:4326). For PostGIS 2.2 and above, any ...
- 2,252
2
votes
1
answer
125
views
Complexify geography polygons in PostGIS
Is it possible to modify polygons (essentially, make them more complex by inserting additional points) so that their naïve point-linking rendering would be a closer match to their respective geodesic ...
- 273
0
votes
1
answer
300
views
Spatial and DateTime join in SQL BigQuery [closed]
I have 2 tables in BQ; one of them includes a geography column PointWKT (gps POINT) and second table includes a geography column BoundaryWKT (POLYGONS & MULTIPOLYGONS). Both tables also include ...
- 205
4
votes
1
answer
125
views
Storing Central European data, which SRS/which data type?
I am creating a PostGIS 2.X data base to store some 500 or so sites as points in it, which are distributed in the northeastern part of Germany, northern Poland and parts of the southern Baltic coast. ...
- 1,598
6
votes
0
answers
799
views
ST_Covers fails on GEOGRAPHY column with GiST index, PostGIS 3.0 [closed]
I'm trying to upgrade to Postgres 12 with PostGIS 3.0. Unfortunately, I can't get my spatial queries to work. There seems to be an issue using ST_Covers on a geography column with a GiST index.
Proof ...
- 231
2
votes
1
answer
72
views
Why is a "geography" type needed at all in PostGIS? [closed]
Let a and b be two points. ST_Distance(a, b) returns their distance.
Instead of having two data types, geometry and geography, that practically overload the meaning of ST_Distance(), we could instead ...
4
votes
1
answer
537
views
Finding all circles point lies in using PostGIS
Suppose you have a task to find all points within a certain radius. That's where you use ST_DWithin which is indexable and it will deal with it.
My task is the opposite from that:
I have a set of ...
- 143
2
votes
1
answer
90
views
How to check for PostGIS Function Support Matrix for new custom EPSG imported from EPSG site
I need a support for CRS that was not available in PostGIS 2.5 so I looked up into the EPSG site they had simple easy straight forward insert query to add EPSG so I did that now how do I check or know ...
- 2,730
1
vote
0
answers
487
views
SQL server 2012 convert Multipolygon geometry to geography - GDA94 / MGA zone 55 coordinate reference system
Is there a way to convert geometry column value which has GDA94 / MGA zone 55 coordinate reference system into Geography value in SQL server 2012.
The layer is a multipolygon layer
We were having the ...
- 11
1
vote
1
answer
743
views
PostGIS view on multiple geometry columns
I have two PostGIS tables, ‘filelinks’ and ‘maricopa_plss_qtrqtrs’. The filelinks table has a geometry type column named ‘geom_p’ and the maricopa_plss_qtrqtrs has a geometry type column named ‘geom’. ...
- 618
5
votes
0
answers
972
views
SP-GiST for geography type in PostGIS
This
CREATE INDEX geography_gdx ON schema.table USING GIST (geog);
works, but
CREATE INDEX geography_gdx ON schema.table USING SPGIST (geog);
gives me
[42704] ERROR: data type geography has no ...
- 663
1
vote
1
answer
4k
views
Convert datatype in Google Earth Engine from double to integer [closed]
How can I convert a variable to another data type using Google Earth Engine? For example I've created a NDVI image in double number (interval value -1.0 to +1.0). I want to save the image in byte (...
- 21
3
votes
0
answers
2k
views
What is geometry(4326), and can you do Cartesian calculations with unprojected ellipsoidal coordinates?
I know Geography(4326) is an unbounded, and unprojected, and covers the entire world with ellipsoidal coordinates. I get that the math on this system is done on a spheroid. I also get that with a ...
- 7,031
0
votes
0
answers
307
views
How to set the extent of a PostGIS geography table?
I have multiple geography tables in PostGIS database. All of them have extents of (-180,-90;180,90), probably in their metadata, which makes editing difficult. I'd like to set the extent to exactly ...
- 249
1
vote
1
answer
1k
views
Pulling Spatial Data in Geography Format From MS SQL Server 2008 Using ogr2ogr
Can the MSSQLSpatial driver support geography fields queried from MS SQL Server 2008 as spatial data?
Background
I have been using unixODBC on an Ubuntu 16.04 machine with ODBC Driver 17 for SQL ...
- 11
3
votes
1
answer
2k
views
SQL WKT draws well with geometry, but not with geography data type
I have a following piece of code
DECLARE @g geometry;
DECLARE @borders geography;
SET @g = geometry::STGeomFromText('SOME WKT', 0);
SET @borders = GEOGRAPHY::STGeomFromText(@g.MakeValid()....
- 131
0
votes
0
answers
843
views
SQL Server - ESPG:4326 data stored in Geometry column
It seems that the Microsoft SQL Server Geography data type is better suited for lat/lon because Geography "stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates."
...
- 241
4
votes
1
answer
10k
views
How to Cast geometry to Geography in SQL script in MS SQL Server?
I have a Table with some spatial Data in MS SQL Server. The datatype of the spatial column is geometry.
I'm trying to get the closest data from an input file using the following query:
DECLARE @g ...
- 41.1k
5
votes
1
answer
946
views
Using use_spheroid=false in ST_DWithin of PostGIS
I have created 2 geography columns and indexed them using GIST. I am looking to use ST_DWithin for a larger dataset and to make it faster trying to use the use_spheroid=false.
SELECT pois.objectid, ...
- 618
2
votes
0
answers
976
views
SQL Geometry type and Geography type conversions/projections
I am working with a replica database managed in Sql Server Management Studio 2016. My geographic data of points, lines, and polygons are projected in WGS_1984_UTM_Zone_17N Meter and SRID of 32617. Its ...
- 61
1
vote
1
answer
77
views
Saving borough boundaries as MultiLineStrings or MultiPolygons?
I am creating a database in PostGIS and I have a column that will save the boundaries of boroughs.
The app will later query the database, asking what points, lines or polygons are inside those ...
- 1,129
8
votes
2
answers
907
views
Is PostGIS geography type EPSG:4326 only?
I was under the impression that the geography type only uses the lat,long based coordinates EPSG:4326. And I didn't remember having to specify a CRS for a geography column.
However, from this ...
- 11k
1
vote
2
answers
415
views
Does transforming postGIS geographic data to EPSG:4326 , makes them geographic?
I am reading this about "Clustering on Indices" and in 25.2 says that
The geohash algorithm only works on data in geographic
(longitude/latitude) coordinates, so we need to transform the
...
- 1,129
2
votes
1
answer
2k
views
Add Geography Column to an existing table
I Have an existing table GPSPosition which has a coma seperated LatLong column. I have attempted to add a geography column using the query:
alter table GPSPositions add geographyColumn as geography:...
- 21
2
votes
1
answer
721
views
Fast(est) way to detect if point is in range of another point? (Geographic Coordinates)
I'm looking for a way to detect if 2 points are "near enough" each other for a match, and hopefully, if the range could be specified in metric system (each item may have different ranges, but there ...
- 21