Display several spatial columns

[rajanski]

Hi postgis-preview is really great! Thanks!

Only I couldnt manage to display two different geometry columns from one query as in

SELECT st_astext(sq.point), sq.point as geom, sq.poly  as geom2
from 
(
select ST_GeomFromText('POINT(-71.064544 42.28787)',4326) as point,
ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))') as poly
) sq;

[rajanski]

Ok reading the bottom note in the readme and pr #17 I see that this is logically not possible (yet)?

[chriswhong]

I would say since the query ends up being converted into geoJSON/topJSON, it's not a good practice to have two geometries in the result set. Postgis preview is looking for one specifically called geom, and that's all it is going to render on the map.

I would suggest you restructure your query so that your geoms are in the same column on different rows by UNIONing together two subqueries.

However, you are also mixing points and polygons in the same response, which may also cause problems.

[rajanski]

Well, imho the tool should take any query no matter what the parameters are and visualize the result as good as possbile.

One approach would be to use the GDAL/OGR libs to:

• pull the data via their ExecuteSQL function and
• then aggregate geometry fields into one GeometryCollection if there is more than one geometry field in the query result.

This approach would also eliminate the need to explicitely name the geometry column.

I am doing exactly the same using the python OGR libs and it works flawlessly.

Can't help much with node though unfortunately

[chriswhong]

I think it is reasonable to require that there is only one geometry per
row, as that is the way most spatial data are imported and exported (and in
order to be rendered in leaflet it must be geojson, which only has one
geometry per row)

It is a simpler proposition to combine your geometries together as part of
your query.

On Monday, August 8, 2016, rajanski [email protected] wrote:

Well, I still have the opinion that the tool should take any query no
matter what the parameters are and visualize the result as good as
possbile.

One approach would be use the GDAL/OGR libs
https://github.com/naturalatlas/node-gdal to pull the data via their
ExecuteSQL function and then aggregate geometry fields into one
GeometryCollection if there is more than one geometry field in the query
result.

I am doing exactly the same using the python OGR libs and it works
flawlessly.

Can't help much with node though unfortunately

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#42 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABv7XOGOJuHO3SWENPc3BQraIlif21w8ks5qdvw2gaJpZM4IyNEj
.

[rajanski]

I still think it's easy to do.

I don't mean to be a smartass, I just think postgis-preview is really great and can be an awesome tool for geospatial devs as well as for teaching people spatial sql. So the simpler and more straight forward it is, the better.

Just save the following code as a html file and run it in a browser (geojson with geometrycollection with multiple geometries in leaflet, working flawlessly)

<!--
<script src="https://api.tiles.mapbox.com/mapbox.js/v1.6.3/mapbox.js"></script>
<link rel="stylesheet" href="https://api.tiles.mapbox.com/mapbox.js/v1.6.3/mapbox.css" />-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/leaflet/0.7.7/leaflet.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/leaflet/0.7.7/leaflet.css" />

<div id="map">
        </div>
<style>
  body {
    margin: 0;
    padding: 0;
  }

  #map {
    position: absolute;
    top: 0;
    bottom: 0;
    width: 100%;
  }
</style>
<script>
var myGeoms = {"type": "FeatureCollection", "features": [{"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[11.9472045, 48.8100315], [11.939592453251128, 48.77176315676349], [11.917915178118655, 48.73932082188134], [11.88547284323651, 48.717643546748874], [11.8472045, 48.7100315], [11.808936156763492, 48.717643546748874], [11.776493821881346, 48.73932082188134], [11.754816546748872, 48.77176315676349], [11.7472045, 48.8100315], [11.754816546748872, 48.84829984323651], [11.776493821881346, 48.88074217811865], [11.80893615676349, 48.90241945325113], [11.8472045, 48.9100315], [11.885472843236508, 48.90241945325113], [11.917915178118655, 48.88074217811866], [11.939592453251128, 48.84829984323651], [11.9472045, 48.8100315]]]}, {"type": "Point", "coordinates": [11.8472045, 48.8100315]}]}, "type": "Feature", "properties": {"gid": 28222, "wname": "III"}, "id": 0}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[11.2499405, 47.711861], [11.242328453251128, 47.67359265676349], [11.220651178118654, 47.64115032188134], [11.18820884323651, 47.61947304674887], [11.1499405, 47.611861], [11.111672156763492, 47.61947304674887], [11.079229821881345, 47.64115032188134], [11.057552546748871, 47.67359265676349], [11.0499405, 47.711861], [11.057552546748871, 47.75012934323651], [11.079229821881345, 47.78257167811865], [11.11167215676349, 47.804248953251125], [11.1499405, 47.811861], [11.188208843236508, 47.804248953251125], [11.220651178118654, 47.78257167811866], [11.242328453251128, 47.75012934323651], [11.2499405, 47.711861]]]}, {"type": "Point", "coordinates": [11.1499405, 47.711861]}]}, "type": "Feature", "properties": {"gid": 28223, "wname": "HHH"}, "id": 1}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[7.5189865, 50.98413], [7.511374453251129, 50.94586165676349], [7.489697178118655, 50.91341932188134], [7.457254843236509, 50.891742046748874], [7.4189865, 50.88413], [7.380718156763491, 50.891742046748874], [7.348275821881345, 50.91341932188134], [7.326598546748872, 50.94586165676349], [7.3189865, 50.98413], [7.326598546748871, 51.02239834323651], [7.348275821881345, 51.05484067811865], [7.380718156763491, 51.07651795325113], [7.4189865, 51.08413], [7.457254843236508, 51.07651795325113], [7.489697178118655, 51.05484067811866], [7.511374453251128, 51.02239834323651], [7.5189865, 50.98413]]]}, {"type": "Point", "coordinates": [7.4189865, 50.98413]}]}, "type": "Feature", "properties": {"gid": 28224, "wname": "GGG"}, "id": 2}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[11.0512295, 52.447803], [11.043617453251128, 52.40953465676349], [11.021940178118655, 52.37709232188134], [10.98949784323651, 52.355415046748874], [10.9512295, 52.347803], [10.912961156763492, 52.355415046748874], [10.880518821881346, 52.37709232188134], [10.858841546748872, 52.40953465676349], [10.8512295, 52.447803], [10.858841546748872, 52.48607134323651], [10.880518821881346, 52.51851367811865], [10.91296115676349, 52.54019095325113], [10.9512295, 52.547803], [10.989497843236508, 52.54019095325113], [11.021940178118655, 52.51851367811866], [11.043617453251128, 52.48607134323651], [11.0512295, 52.447803]]]}, {"type": "Point", "coordinates": [10.9512295, 52.447803]}]}, "type": "Feature", "properties": {"gid": 28225, "wname": "FFF"}, "id": 3}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[9.472157, 52.7944455], [9.464544953251128, 52.75617715676349], [9.442867678118654, 52.723734821881344], [9.41042534323651, 52.702057546748875], [9.372157, 52.6944455], [9.333888656763492, 52.702057546748875], [9.301446321881345, 52.723734821881344], [9.279769046748871, 52.75617715676349], [9.272157, 52.7944455], [9.279769046748871, 52.83271384323651], [9.301446321881345, 52.86515617811865], [9.33388865676349, 52.88683345325113], [9.372157, 52.8944455], [9.410425343236508, 52.88683345325113], [9.442867678118654, 52.86515617811866], [9.464544953251128, 52.83271384323651], [9.472157, 52.7944455]]]}, {"type": "Point", "coordinates": [9.372157, 52.7944455]}]}, "type": "Feature", "properties": {"gid": 28226, "wname": "EEE"}, "id": 4}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[11.26228, 52.1873125], [11.25466795325113, 52.14904415676349], [11.232990678118655, 52.11660182188134], [11.20054834323651, 52.09492454674887], [11.16228, 52.0873125], [11.124011656763493, 52.09492454674887], [11.091569321881346, 52.11660182188134], [11.069892046748873, 52.14904415676349], [11.06228, 52.1873125], [11.069892046748873, 52.22558084323651], [11.091569321881346, 52.25802317811865], [11.124011656763491, 52.279700453251124], [11.16228, 52.2873125], [11.200548343236509, 52.279700453251124], [11.232990678118655, 52.258023178118655], [11.25466795325113, 52.22558084323651], [11.26228, 52.1873125]]]}, {"type": "Point", "coordinates": [11.16228, 52.1873125]}]}, "type": "Feature", "properties": {"gid": 28227, "wname": "DDD"}, "id": 5}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[11.534387, 53.1231375], [11.526774953251127, 53.08486915676349], [11.505097678118654, 53.05242682188134], [11.472655343236509, 53.03074954674887], [11.434387, 53.0231375], [11.396118656763491, 53.03074954674887], [11.363676321881345, 53.05242682188134], [11.341999046748871, 53.08486915676349], [11.334387, 53.1231375], [11.341999046748871, 53.16140584323651], [11.363676321881345, 53.19384817811865], [11.39611865676349, 53.215525453251125], [11.434387, 53.2231375], [11.472655343236507, 53.215525453251125], [11.505097678118654, 53.19384817811866], [11.526774953251127, 53.16140584323651], [11.534387, 53.1231375]]]}, {"type": "Point", "coordinates": [11.434387, 53.1231375]}]}, "type": "Feature", "properties": {"gid": 28228, "wname": "CCC"}, "id": 6}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[14.6116045, 52.580904], [14.603992453251129, 52.54263565676349], [14.582315178118655, 52.51019332188134], [14.54987284323651, 52.48851604674887], [14.5116045, 52.480904], [14.473336156763493, 52.48851604674887], [14.440893821881346, 52.51019332188134], [14.419216546748872, 52.54263565676349], [14.4116045, 52.580904], [14.419216546748872, 52.61917234323651], [14.440893821881346, 52.65161467811865], [14.47333615676349, 52.67329195325112], [14.5116045, 52.680904], [14.549872843236509, 52.67329195325112], [14.582315178118655, 52.651614678118655], [14.603992453251129, 52.61917234323651], [14.6116045, 52.580904]]]}, {"type": "Point", "coordinates": [14.5116045, 52.580904]}]}, "type": "Feature", "properties": {"gid": 28229, "wname": "BBB"}, "id": 7}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[14.3730165, 52.6204625], [14.365404453251129, 52.58219415676349], [14.343727178118655, 52.549751821881344], [14.31128484323651, 52.528074546748876], [14.2730165, 52.5204625], [14.234748156763493, 52.528074546748876], [14.202305821881346, 52.549751821881344], [14.180628546748872, 52.58219415676349], [14.1730165, 52.6204625], [14.180628546748872, 52.65873084323651], [14.202305821881346, 52.69117317811865], [14.23474815676349, 52.71285045325113], [14.2730165, 52.7204625], [14.311284843236509, 52.71285045325113], [14.343727178118655, 52.69117317811866], [14.365404453251129, 52.65873084323651], [14.3730165, 52.6204625]]]}, {"type": "Point", "coordinates": [14.2730165, 52.6204625]}]}, "type": "Feature", "properties": {"gid": 28230, "wname": "AAA"}, "id": 8}, {"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Polygon", "coordinates": [[[11.5816665, 49.0271935], [11.574054453251128, 48.98892515676349], [11.552377178118654, 48.956482821881345], [11.51993484323651, 48.934805546748876], [11.4816665, 48.9271935], [11.443398156763491, 48.934805546748876], [11.410955821881345, 48.956482821881345], [11.389278546748871, 48.98892515676349], [11.3816665, 49.0271935], [11.389278546748871, 49.06546184323651], [11.410955821881345, 49.097904178118654], [11.44339815676349, 49.11958145325113], [11.4816665, 49.1271935], [11.519934843236507, 49.11958145325113], [11.552377178118654, 49.09790417811866], [11.574054453251128, 49.06546184323651], [11.5816665, 49.0271935]]]}, {"type": "Point", "coordinates": [11.4816665, 49.0271935]}]}, "type": "Feature", "properties": {"gid": 28231, "wname": "JJJ"}, "id": 9}]};
/*
var map = L.mapbox.map('map', null)
  .setView([11.552377178118654, 49.09790417811866], 12);
*/
var map = L.map('map', {
            zoomControl:true, maxZoom:28, minZoom:1
        }).fitBounds([[47.19468585,5.41481305869],[55.01618415,15.6958059413]]);

L.geoJson(myGeoms).addTo(map);
</script>```

[seabre]

Just putting my two cents in. IMHO, it's usually easy enough to just UNION your queries together.

e.g. @rajanski your query can be written to include both of your features as follows:

SELECT ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))', 4326) as geom

UNION

SELECT ST_GeomFromText('POINT(-71.064544 42.28787)', 4326) as geom

Honestly, I'd rather the preview work as is and not try to make too many assumptions with your data.

I don't think telling people to UNION their queries together is too big of an inconvenience. Some documentation of some sort and an example should be good.

[rajanski]

@seabre I get your point . Anyway I was looking at postgis-preview from a point of teaching SQL and postgis. In this context I do still think it would be better to make it behave like any other postgresql query client.

[chriswhong]

I think the original intent was to behave like CartoDB, where only one geometry column is expected. IMHO it becomes more confusing to allow multiple geometry columns, as it may not be clear which one is being rendered.

[rajanski]

They are all rendered as geomcollection

[chriswhong]

I think we should stick to a single geometry column with a specific name for the moment, if you still feel strongly about this, feel free to re-open.