I would like to store GeoJSON data into PostgreSQL database.  WITH data AS (SELECT '{ "type": "FeatureCollection",     "features": [       { "type": "Feature",         "geometry": {"type": "Point", "coordinates": [102.0, 0.5]},         "properties": {"prop0": "value0"}         },       { "type": "Feature",         "geometry": {           "type": "LineString",           "coordinates": [             [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]             ]           },         "properties": {           "prop0": "value0",           "prop1": 0.0           }         },       { "type": "Feature",          "geometry": {            "type": "Polygon",            "coordinates": [              [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0],                [100.0, 1.0], [100.0, 0.0] ]              ]          },          "properties": {            "prop0": "value0",            "prop1": {"this": "that"}            }          }        ]      }'::json AS fc)  SELECT   row_number() OVER () AS gid,   ST_AsText(ST_GeomFromGeoJSON(feat-&gt;&gt;'geometry')) AS geom,   feat-&gt;'properties' AS properties FROM (   SELECT json_array_elements(fc-&gt;'features') AS feat   FROM data ) AS f;    I can query but I need to save data as a table. Does anyone know how to store this kind data into a table in PostgreSQL.?  Reference to   This reference does only query but I need save the layer as a table.
122252
I am new to GeoJSON. I have a GeoJSON features collection as shown and would like to store it in postgres table (testtable). My postgres table has a serial id and geometry column.  {     "type": "FeatureCollection",     "features": [         {             "type": "Feature",             "geometry": {                 "type": "Point",                 "coordinates": [                     2565453.1826721914,                     -3835048.659760314                 ]             }         },         {             "type": "Feature",             "geometry": {                 "type": "LineString",                 "coordinates": [                     [                         2727584.7219710173,                         -3713449.1942418693                     ],                     [                         2732476.691781269,                         -3992291.473426192                     ]                 ]             }         },         {             "type": "Feature",             "geometry": {                 "type": "Polygon",                 "coordinates": [                     [                         [                             2442627.9025405287,                             -3705499.954308534                         ],                         [                             2425506.008204649,                             -3886502.837287831                         ],                         [                             2425506.008204649,                             -3886502.837287831                         ],                         [                             2555143.2081763083,                             -3910962.686339088                         ],                         [                             2442627.9025405287,                             -3705499.954308534                         ]                     ]                 ]             }         }     ] }   I would like to insert the GeoJSON data into the table testtable.   How do I go about it?  I am using postgres version 9.3.5 with postgis version 2.1.3    I have been directed to previously asked questions which answer how to store a single feature eg a point or polygon. My question asks how to save multiple features in the GeoJSON file. By multiple features I mean a mixture of points, lines and polygon feature types in one file.
11815
I have a PostGIS database and have successfully consumed a table into GeoServer and published a WFS.  Are there REST endpoints available for these WFS' akin to ESRI's feature services?  How are parameters to be constructed?
45299
I have a query like:  SELECT a.id, a.name, json_agg(b.*) as "item"   FROM a   JOIN b ON b.item_id = a.id  GROUP BY a.id, a.name;   How can I select the columns in b so I don't have b.item_id in the JSON object?  I have read about , but it returns a JSON object like:  {"f1": "Foo", "f2": "Bar"}   I would need to remap the JSON object once it is fetched to match the proper column keys. I'd like to avoid that and keep original column names.
53926
How do i create a linestring from points sort by date in PostgreSQL?  I tried:  SELECT observations.id,    ST_MakeLine(observations.geom ORDER BY observations.date) AS newgeom    FROM   observations     GROUP BY    observations.id;   This is going wrong. It returns me a linestring with same coordinates.  So, instead of a line from a to b, it gives me a to a.    What am I doing wrong?
79328
I'm currently trying to create polygons that represent the areas surrounded by roads. The roads are stored in PostGIS as points / lines so I have flexibility over what I can do with them.  I'm basically trying to turn the white areas of this example line output into polygons:    Any ideas?    Here is the PostGIS to achieve this (assuming you have a road table full of lines):  SELECT (ST_Dump(ST_Polygonize(roads.geom))).geom AS the_geom FROM (SELECT ST_Transform(ST_SetSRID(geom, 27700), 4326) AS geom FROM road_lines) AS roads
112978
I have a WFS layer:  myVecLayer_disp= new OpenLayers.Layer.Vector("Редактируемый участок",{projection: new OpenLayers.Projection("EPSG:900913")});   And i have feature store:          var protocol=new OpenLayers.Protocol.HTTP({             url: url_servlet+"fullSearch.jsp",             format: new OpenLayers.Format.GeoJSON({internalProjection: new OpenLayers.Projection("EPSG:4326"),externalProjection:new OpenLayers.Projection("EPSG:900913")})     });     searchStore = new GeoExt.data.FeatureStore({         layer: myVecLayer_disp,         fields: [             {name: 'id', type: 'String'},             {name: 'name_dispatcher', type: 'String'},             {name: 'voltage', type: 'String'},         ],         proxy: new GeoExt.data.ProtocolProxy({             protocol: protocol,         }),         autoLoad: true,         layerOptions: function() {             return {                 strategies: [new OpenLayers.Strategy.BBOX()],                 //projection:"EPSG:4326"             };         }     });   As you see i use GeoJSON. In this geojson i have features in wgs84. And reproject it in mercator in protocol. Now i show this layer:      app.mapPanel.map.addLayers(myVecLayer_disp); myVecLayer_disp.visibility=true;   And see features near Africa but expect to see it within Russia. So Nothig reproject i think. But when i see layes's features:      LINESTRING(0.00055807640304122 0.00050642147755534,0.00055807616948463  0.00050641930362077,0.00055807588202664 0.00050641664462617,0.00055807423809849  0.00050641332981391,0.00055807306129967 0.00050640979946183,0.00055807191147579  0.00050640644872202,0.00055807052802261 0.00050640231648177,0.00055806910873212  0.00050639813934481,0.00055806841697321 0.00050639609116937,0.00055806763547558  0.00050639388128181)   Ant this is same feature in geojson:  {"type":"LineString","coordinates":[[62.124781001642077,56.374581001212391], [62.124755002241727,56.374338999945053],[62.124723002564679,56.374043002482722], [62.124540001320156,56.373673997157425],[62.12440900067503,56.373281000617851], [62.124281002866155,56.37290799870533],[62.124126997563053,56.372447999975407], [62.123969002867341,56.371983002813522],[62.123891996618511,56.371755002191776], [62.123805000700372,56.371508997142655]]}   What i doing wrong?
46082
CartoDb Recommend  that the following query is faster than an St_intersects Query  SELECT * FROM {table_name} WHERE the_geom &amp;&amp; ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ST_Point(-73.995, 40.723)), 4326)   I have the following Query that works just fine and returns GeoJson as needed,   WITH c AS (SELECT -73.976440429688, 40.743095232182, -73.970947265625, 40.738933241136, 4326 As srid)  , bbox AS (SELECT ST_Transform ( ST_MakeEnvelope( -73.976440429688, 40.743095232182 ,-73.970947265625 , 40.738933241136 , 4326) , 900913 ) As geom FROM c ) SELECT row_to_json(fc)     FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features     FROM (SELECT 'Feature' As type, osm_id As id,         ( SELECT row_to_json(t)           FROM (SELECT osm_id, "name" ) t )As properties,          ST_AsGeoJSON(ST_Transform (lg.way ,4326 ) )::json As geometry      FROM planet_osm_polygon As lg, bbox         WHERE ( building is NOT NULL) AND ST_Intersects(lg.way, bbox.geom)             ) As f ) fc;                   I have been trying to create the same JSon result using ST_MakeBox2D in a similar fasion to the CartoDb example and failing miserably.  My objective is to have the fastest possible query for any geometry that any part of itself WITHIN the bounding box.  My Questions are:   Which method will return the fastest possible result, assuming that the Columns in the WHERE clause are indexed properly? How do I change my query to use St_makeBox2D with the geom &amp;&amp; syntax as per the CartoDb Example?
58318
I would like to load a geoJSON (polygon) file into my leaflet map. I have seen examples where geoJSON is embedded into the javascript code but I can't find any examples showing how it is done with an external file.   &lt;head&gt;     &lt;meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"&gt;     &lt;link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.5/leaflet.css" /&gt;     &lt;script src="usStates.geojson" type="text/javascript"&gt;&lt;/script&gt;     &lt;style&gt;         html, body, #map {             height: 100%;         }         body {             padding: 0;             margin: 0;         }     &lt;/style&gt; &lt;/head&gt;  &lt;body&gt;     &lt;div id="map" style="height: 100%"&lt;/div&gt;     &lt;script src="http://d3js.org/d3.v2.min.js?2.9.3"&gt;&lt;/script&gt;     &lt;script src="http://cdn.leafletjs.com/leaflet-0.5/leaflet.js"&gt;&lt;/script&gt;      &lt;script type="text/javascript"&gt;       var map = L.map('map').setView([38.57, -94.71], 4);      L.tileLayer('http://{s}.tile.cloudmade.com/9067860284bc491e92d2342cc51d47d9/998/256/{z}/{x}/{y}.png', {attribution: 'Map data &amp;copy; &lt;a href="http://openstreetmap.org"&gt;OpenStreetMap&lt;/a&gt; Imagery © &lt;a href="http://cloudmade.com"&gt;CloudMade&lt;/a&gt;'}).addTo(map);      var featureStyle = {         "color": "#ff7800",         "weight": 5,          "opacity": 0.2     };      L.geoJson(usStates).addTo(map);      &lt;/script&gt;  &lt;/body&gt;
88601
I'm currently setting up a fresh install of PostGIS 2.0.2 and PostgreSQL 9.1.6 on Ubuntu.  I've recently come across   indicating that using the public schema to store all data is not a good idea.  For this reason, I've set up a schema called data and made myself the owner, but is it a good idea?  My concerns are:   Besides setting the owner, I may need to pay attention to things on the Privileges tab when creating this new schema (through pgAdmin III); I may not get the same benefits by storing my data in the public schema and dumping all data into a separate schema before doing a backup/restore (this would save a few keystrokes when using ogr2ogr); and I may run into trouble by not having the default PostGIS tables and views in my new data schema (they are in the public schema within the same database).
107537
Given the table:      Column    |            Type               id           | integer                       latitude     | numeric(9,6)                  longitude    | numeric(9,6)                  speed        | integer                       equipment_id | integer                       created_at   | timestamp without time zone Indexes:     "geoposition_records_pkey" PRIMARY KEY, btree (id)   The table has 20 million records that is not, relatively speaking, a large number. But it makes sequential scans slow.  How can I get the last record (max(created_at))  of each equipment_id?   I've tried both the following queries, with several variants that I've read through many answers of this topic:  select max(created_at),equipment_id from geoposition_records group by equipment_id;  select distinct on (equipment_id) equipment_id,created_at    from geoposition_records order by equipment_id, created_at desc;   I have also tried creating btree indexes for equipment_id,created_at but Postgres finds that using a seqscan is faster. Forcing enable_seqscan = off is of no use either since reading the index is as slow as the seq scan, probably worse.  The query must run periodically returning always the last.  Using Postgres 9.3.  Explain/analyze (with 1.7 million records):  set enable_seqscan=true; explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id; "HashAggregate  (cost=47803.77..47804.34 rows=57 width=12) (actual time=1935.536..1935.556 rows=58 loops=1)" "  -&gt;  Seq Scan on geoposition_records  (cost=0.00..39544.51 rows=1651851 width=12) (actual time=0.029..494.296 rows=1651851 loops=1)" "Total runtime: 1935.632 ms"  set enable_seqscan=false; explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id; "GroupAggregate  (cost=0.00..2995933.57 rows=57 width=12) (actual time=222.034..11305.073 rows=58 loops=1)" "  -&gt;  Index Scan using geoposition_records_equipment_id_created_at_idx on geoposition_records  (cost=0.00..2987673.75 rows=1651851 width=12) (actual time=0.062..10248.703 rows=1651851 loops=1)" "Total runtime: 11305.161 ms"
66903
I'm coding an app with Phonegap, which requests user's current position. As an overlay I have loaded a geojson layer. I want the app to automatically extract a value of said layer at the exact location, where the user currently is (without the user manually clicking on the layer).  I've tried Leaflet PIP extension, but couldn't get it to work with leaflet 1.0.3. I've also tried simulating a touch/mouseclick event at screen coordinates, but that didn't work either.  I've found the extension Leaflet.PointInPolygon, which can check, if a point is lying within a polygon. Therefore a transformation from geojson to an array with coords is required.  I wrote the following code to parse my geojson and write it into arrays to add polygon layers to my map (in order to check later, if map.getCenter() coords are within any of these):  var coord = []; for (var i = 0; i &lt; data.features.length; i++) {   feat = data.features[i];    for (var j = 0; j &lt; feat.geometry.coordinates.length; j++) {     coord = feat.geometry.coordinates[j];      var polygon = L.polygon(coord, { color: "red" }).addTo(map);   } }   I'm facing the prolem now  that lat and lon are in the wrong order:  [[10.77932197941255,52.03584196145097],[10.77932197941255,52.03575906909681],[10.779636389761993,52.03575906909681],[10.779636389761993,52.03584196145097],[10.77932197941255,52.03584196145097]]   How can I fix this?  Is there maybe a leaflet extension or javascript framework, which can directly check, if given coordinates are within a geojson object?
98711
When I try to access a JSON element using -&gt;&gt;'elementName' I get a texttype.  SELECT pg_typeof(x1-&gt;&gt;'a'), jsonb_typeof(x2) FROM ( VALUES   ('{"a":5}'::jsonb, '5'::jsonb) ) AS t(x1,x2);   pg_typeof | jsonb_typeof  -----------+--------------  text      | number (1 row)   However, ...     When converting textual JSON input into jsonb, the primitive types described by RFC 7159 are effectively mapped onto native PostgreSQL types, as shown in Table 8-23.   Here is the table reproduced from the docs,  Table 8-23. JSON primitive types and corresponding PostgreSQL types  JSON primitive type     PostgreSQL type  Notes string                  text             \u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8 number                  numeric          NaN and infinity values are disallowed boolean                 boolean          Only lowercase true and false spellings are accepted null                    (none)           SQL NULL is a different concept
98892
I am displaying 100 000 points in a layer via WFS protocol. I need about 5 minutes. However, 100 000 does not seems so much...  My points are stored in PostGIS, in a column defined this way : AddGeometryColumn('records','position', 4326, 'POINT', 2)  My GeoServer server tell me almost instantly this:   outputFormat = text/xml; subtype=gml/3.1.1     resultType = results   So I think it it OK for reading in PostGIS, and send the response to my browser.  But my Firefox/Chrome are very long to display it, so much that it tells me that the page is unresponsive many times. So maybe it is my JavaScript or my browser who is long to display. Here is my JavaScript with OpenLayers:  var wfs = new OpenLayers.Layer.Vector("Record2", {                     strategies: [new OpenLayers.Strategy.Fixed()],                     protocol: new OpenLayers.Protocol.WFS({                               version: "1.1.0",                     url: "http://localhost:8080/geoserver/wfs",                                 featurePrefix: "cite",                     featureType: "record",                     featureNS: "http://www.openplans.org/cite",                     srsName: "EPSG:4326",                         geometryName: ""                   })                   });          map.addLayers([world, wfs]);         map.zoomToMaxExtent();   Do you have an idea of the reason for this?
114934
I have data that spreads across two UTM Zones GDA/MGA94 UTM Zone 55 and 56. I have layers such as a cadastre, roads, pipelines etc where I want to store measurements like lengths and areas in metres or some kind of metric notation, not degrees!  Whats the best way to manage this in PostGIS (i'm using PostGreSQL 8.4 PostGIS 1.5)?  Should I be storing my data as GDA94 Geographic coords and using some sort of work around to calculate the measurements I require?  Or is there another way to handle this?
118695
I am trying to save a GeoJSON file in QGIS as EPSG:4326, but it keeps being saved as CRS84.   EPSG:4326 and CRS84 are the same except CRS84 saves coordinates as long, lat - and EPSG:4326 saves them as lat, long.   When I open the saved file in QGIS it's read as EPSG:4326, but really the file is CRS84. I know this because when I open the GeoJSON file in notepad it's listed as CRS84, and when I load the file in Shapely it's recognized as CRS84.   What's going on here?  I've tried this on both QGIS 3.6.3 and QGIS 3.4.8 with the same result.
35875
How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance? Considering the below &quot;Test Query&quot;, help me to clarify the following:  As the SubYear is 2050 which is a future date on which there are no cases available yet does it require a full table scan or is there any alternative approach to tune this ?  When the same query is modified to retrieve the data with &quot;SubYear&quot; &lt; 2050, there are lot of results i.e. 35k+. How does PostgreSQL works on it when the Offset is set to a high number lets say 30k ?   Test Query : select     * from     sub_test df,     jsonb_array_elements(json_data) jba where     (jba-&gt;&gt;'name')::text = 'SubYear'     and (jba-&gt;&gt;'value')::numeric &gt; '2050';
10856
I'm building an application that is supposed to query and return every Record in a table that is X kilometers away from PointX. Records and PointX's positions are determined from (long/lat) information provided by Google Geocode API. I'm new to PostGIS. After a quick research, I've found . The answer seems to be along the lines of: SELECT * FROM your_table WHERE ST_Distance_Sphere(the_geom, ST_MakePoint(your_lon,your_lat)) &lt;= radius_mi * 1609.34  The problem is that even though I'm only getting started at GIS, when I look at the above query, I can't possibly imagine how this can use an index. There are 2 function calls. I imagine the table being scanned for every Record. Does PostGIS have any index type capable of making the above query performant and, if not, what would be the recommended approach doing what I need?