Skip to main content
99 votes
Accepted

What is the purpose of PostGIS on PostgreSQL?

If you re-wound the universe to early 2001, and not only let the inventors of PostGIS see the future, but also let the PSC of PgSQL see the future, perhaps PostGIS would be a series of patches on ...
Paul Ramsey's user avatar
  • 19.9k
34 votes

What is the purpose of PostGIS on PostgreSQL?

That's simply not true, PostgreSQL does not support Spatial data types. It supports geometric types. These are perfectly fine for some things, but they're totally separate from real world coordinate ...
Evan Carroll's user avatar
  • 7,081
24 votes
Accepted

Is the Geometry(4326) an alias for Geography datatype?

tl;dr: No. But see the update at the end. GEOMETRY and GEOGRAPHY are different PostgreSQL TYPES; exposed as higher level (SQL), user-defined composite types to the psql environment, but implemented ...
geozelot's user avatar
  • 30.3k
12 votes
Accepted

Export geography field from SQL for use in GIS

You should be able to use STAsText: https://msdn.microsoft.com/en-us/library/bb933977.aspx So you could create a new table: CREATE TABLE wkt_export as SELECT *, RoadGeometry.STASText() as WKT FROM ...
HeikkiVesanto's user avatar
11 votes

Get latitude and longitude of a geography column

This is what ST_DumpPoints is for: SELECT ST_X((dp).geom) AS lon, ST_Y((dp).geom) AS lat FROM ( SELECT ST_DumpPoints( ST_GeogFromText('MULTILINESTRING((-121.382859 41.9831,-121.382849 41....
alphabetasoup's user avatar
8 votes

What is the purpose of PostGIS on PostgreSQL?

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL. SELECT superhero.name FROM ...
whyzar's user avatar
  • 12.1k
7 votes
Accepted

Is PostGIS geography type EPSG:4326 only?

Based on PostGIS website, 4326 used to be the only supported CRS for geography types, but not any more: Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat (SRID:4326). For ...
laser's user avatar
  • 966
6 votes
Accepted

Distance from point stored in geometry with SRID 4326

You want to use ST_DWithin() which takes advantage of indexing SELECT * FROM adresse WHERE ST_DWithin(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography, 200) ORDER BY ...
kttii's user avatar
  • 3,630
6 votes
Accepted

Inserting lat/long data into geography column of SQL Server 2012?

You should create a GEOMETRY data type column in your table to store the coordinates as spatial objects. And I'll assume you have your coordinate pair values in columns called LAT and LON... and I'm ...
DPSSpatial_BoycottingGISSE's user avatar
6 votes

How to Cast geometry to Geography in SQL script in MS SQL Server?

I'm not sure this is the most efficient way of doing it, but it certainly works: SELECT geography::STGeomFromText([geom].STAsText(),4326) where 4326 is the spatial reference ID (SRID)
PeterS's user avatar
  • 678
5 votes
Accepted

Geography for Sql Server STIsValid() getting error

STIsValid() was added to the Geography type with SQL Server 2012. If you are using SQL Server 2008 this method only exists for the Geometry data type
Peter Horsbøll Møller's user avatar
5 votes

Is PostGIS geography type EPSG:4326 only?

While what laser says is true, I believe the reason for the SRID appearing as 0 is due to a decision made by Postgis developers that an undefined SRID should be reported as 0 -- there was a debate, in ...
John Powell's user avatar
  • 13.7k
5 votes
Accepted

Using use_spheroid=false in ST_DWithin of PostGIS

Just set it to false without naming it. ST_DWithin(polygon.geog,pois.geog, 10* 1609.34, false)
JGH's user avatar
  • 42.1k
5 votes
Accepted

Storing Central European data, which SRS/which data type?

As a summary of the comments; I urge you to read more on the topic GEOMETRY vs. GEOGRAPHY, as this is only a limited comparison: PostGIS' type difference between GEOMETRY & GEOGRAPHY is mainly ...
geozelot's user avatar
  • 30.3k
5 votes
Accepted

ogr2ogr cannot import into table with two geography columns

You may try the following (tested) ogr2ogr -nln spatial.buildings -dialect SQLite -sql "SELECT geometry AS footprint FROM RhodeIsland" -update -append -gt 65536 -f "PostgreSQL" PG:...
ThomasG77's user avatar
  • 30.9k
4 votes
Accepted

How to set my ESPG to geography POINT in PostGIS?

If you use a projection, you have to use the geometry type instead. The geography type is only valid for coordinates in WGS 84 GCS. More information can be found here: Difference between geometry ...
Greg Z's user avatar
  • 827
3 votes
Accepted

Transforming geographical points to geometry in PostGIS?

It appears that you are passing the coordinates in (lat lon) order instead of (lon lat) order. ST_GeogFromText :" Points are always expressed in long lat form" The record with ID = 1 your original ...
gm70560's user avatar
  • 1,402
3 votes
Accepted

Choosing target CRS value when doing geographic calculation?

The OGC WMS standard describes a number of "automatic" projections that are centred on the points provided as part of the name. This allows a user to request a map that has an optimal projection for ...
Ian Turton's user avatar
  • 81.8k
3 votes

Inserting lat/long data into geography column of SQL Server 2012?

Make a user-defined function (UDF): CREATE FUNCTION [dbo].[GetGeoFromLatLng] ( @lat decimal(9,6), @lng decimal(9,6) ) RETURNS geography WITH SCHEMABINDING AS BEGIN DECLARE @ret geography ...
codeMonkey's user avatar
3 votes
Accepted

ERROR: Only lon/lat coordinate systems are supported in geography

I think the problem is that your st_intersects mixes a geography and a geometry; I guess that Postgres is trying to cast your geometry to geography, and that fails because srid 2877 is, as your error ...
mlinth's user avatar
  • 550
3 votes

Fast(est) way to detect if point is in range of another point? (Geographic Coordinates)

If you are using PostGIS as per your comments, then read this. It will tell you the best function to use in your case for speed. ST_DWithin will use geography data types and so can calculate on a ...
MappaGnosis's user avatar
  • 33.9k
3 votes

Does transforming postGIS geographic data to EPSG:4326 , makes them geographic?

I think you're confusing coordinate systems and data types. Geometry and geography are data types in PostGIS, which can have one of a number of coordinate systems, of which EPSG:4326 is one. EPSG:4326 ...
Emily's user avatar
  • 704
3 votes

Pulling Spatial Data in Geography Format From MS SQL Server 2008 Using ogr2ogr

Telling ogr the tables in the connection string made no positive effect for me. The solution for my issue, which looks to be the similar to yours, was to make an explicit cast of the shape field. ...
Steve's user avatar
  • 1,331
3 votes

Convert datatype in Google Earth Engine from double to integer

Probably you would like something like this: // Calculate NDVI for a Landsat 5 image var NDVI = image.normalizedDifference(['B4', 'B3']); var scaledNDVI = NDVI.multiply(125).add(128); print(...
Kuik's user avatar
  • 10.1k
3 votes
Accepted

PostGIS view on multiple geometry columns

If you've already created the view, you cannot create/replace it and add a new column. You must either drop the view then re-create it or create a view with a different name.
pdavis's user avatar
  • 827
3 votes

Why is a "geography" type needed at all in PostGIS?

The Paragraph 4.2.2 in PostGIS manual, I think it describes the reasoning behind your first q: The geography type allows you to store data in longitude/latitude coordinates, but at a cost: there ...
nickves's user avatar
  • 11.5k
3 votes
Accepted

Complexify geography polygons in PostGIS

I think you can use ST_Segmentize to do this (especially if you have Geography objects). Returns a modified geometry having no segment longer than the given max_segment_length. Distance computation ...
Ian Turton's user avatar
  • 81.8k
3 votes
Accepted

How to get most accurate Point-Point distance queries in Europe with Spatialite

The SpatiaLite function for distance measurement is more advanced nowadays. It's a pity that search engines do not find the up-to-date documentation about the SQL functions of SpatiaLite www.gaia-gis....
user30184's user avatar
  • 66.4k
3 votes

Geography in GeoPandas?

GeoPandas currently supports only planar operations as its geometry engine (shapely/pygeos) is planar. There is a vague discussion to bring support of spherical geometry in the future but based on the ...
martinfleis's user avatar
  • 2,427
3 votes

Inserting geography data types with GeoPandas

The PL/pgSQL function is looking for a SRID in varchar format, and you have your srid as an integer. Should work if you change it to this: with engine.connect() as conn: gdf.to_postgis('my_data', ...
Encomium's user avatar
  • 3,143

Only top scored, non community-wiki answers of a minimum length are eligible