Idea: PostGIS support

[gijs]

Since charts are already possible, wouldn't it be a great addition to draw records with a valid geom column on a (canvas/webgl/mapnik) map?

I'm sure it's a lot harder to implement than a chart, esp. with projections and things like that... but a basic version should be feasible. I'll try and build it myself if I find some spare time.

[sasha-alias]

I've never worked with PostGIS to be honest, but since there is already used an awesome d3 visualisation library in SQL Tabs, I think it's quite possible. What I would need for that is a more detailed user story, i.e. an example of a dataset and the way it should be visualised.

[jackrsteiner]

Agree. This would be totally awesome. It would be very much like SharpGIS's spatial query viewer or Tatukgis's Viewer or Manifold's Viewer, all of which do not work on multiple platforms.

You can see how Manifold's interface works on youtube.

With sqltabs style interface, and some arbitrary spatial table, a query might look something like:

--- map basic
SELECT
    ST_asgeojson(geom),
    name
FROM some_table
WHERE population > 10000000;

It looks like Leaflet can pretty easily visualize geojson.

[sasha-alias]

Technically to draw map and geo objects should be possible. D3 framework supports geojson and topojson. I'm not sure how hard to write convertion from postgis to geojson/topojson in JavaScript though. I guess the convertor should be the first step to the goal.

[jackrsteiner]

Sasha, I'd like to look more into trying this. Can you point me toward an analogous "converter" in javascript, for something other than postgis to geojson, so I can better understand what's needed? Postgis can convert to geojson using the ST_AsGeoJSON function.

[gijs]

@jackrsteiner I had a look at this yesterday and found out about the renderMap() function in sasha-alias/sqldoc (which is used by sqltabs to render the results pane).

I'd prefer to use react-leaflet (since React is already part of the project), or plain Leaflet.

One thing I couldn't figure out is how to detect if the connected database supports spatial queries?

[gijs]

Thinking out loud:

Query:

--- map points
SELECT ST_AsGeoJSON(geom), name FROM some_table WHERE population > 1000000;

Output:

(100 rows)
#  geom                                               name
1  {"type": "Feature", "geometry": { type: "Point",   "Some name"
2  {"type": "Feature", "geometry": { type: "Point",   "Some name"
3  {"type": "Feature", "geometry": { type: "Point",   "Some name"
...

Dataset passed to react-leaflet:

...
render() {
  const { data, position, zoom } = this.props;
  // 'data' contains the data from the query, and needs to be wrapped in a FeatureCollection.
  // Coordinates need to be in EPSG:4326 et cetera...
  return (
    <Map center={position} zoom={zoom}>
      <GeoJSON data={data} />
    </Map>
  );
}
...

[sasha-alias]

@gijs you are right, renderMap was supposed to be used for maps rendering but I didn't succeed in implementation at that time. The design was as simple as:

--- chart map
SELECT ...

I don't mind using any license compatible lib, react-leaf is MIT so if it does the job then it should be totally fine. Unfortunately I can't help much with implementation since I don't have any experience with spatial data, but I'm here to help you with any SQLTabs internals.

One thing I couldn't figure out is how to detect if the connected database supports spatial queries?

You probably shouldn't care about it, if DB doesn't support spatial queries it will return exception so the dataset will be different and sqldoc will render error.

If you need to test changes in sqldoc together with sqltabs I usually use the following approach:

prerequisite:

npm install -g install-local

terminal 1:

cd sqldoc
npm run build:watch

terminal 2:

cd sqltabs
npm run build:watch

terminal 3:

cd sqltabs
install-local ../sqldoc/ ;  npm start

[jackrsteiner]

@gijs I'm not sure that it makes sense to specify the geometry type after the map trigger. Geometry types are already specified in the GeoJSON object, and Leaflet parses these. But maybe I'm missing something.

I do like the idea that the map keyword could be followed by styling and/or tileLayer keywords -- so perhaps a basic style template/tile is defined and set as the default, and additional templates and tileLayers could be added later. This might be in the form of ---map provider basemap.

So, for example:

--- map carto dark 
SELECT ST_AsGeoJSON(geom), name FROM some_table WHERE population > 1000000;

or, alternatively:

--- map mapbox street
SELECT ST_AsGeoJSON(geom), name FROM some_table WHERE population > 1000000;

Thanks!

[jackrsteiner]

But then again, maybe you don't want to add that complexity.

[jackrsteiner]

Leaflet-providers might be valuable, if you want to make different providers available.

[jackrsteiner]

@sasha-alias I'm having trouble setting up the sqldoc development environment. I'm more or less following the steps you describe above, but I get this error inside sqltabs when I try to run a query:

SQLTABS ERROR. Please report the issue at https://github.com/sasha-alias/sqltabs/issues :
ReferenceError: rword is not defined
    at Constructor.renderTable (/Users/jac/Documents/sqltabsdev/sqltabs/node_modules/sqldoc/build/SqlDoc.js:605:19)
    at /Users/jac/Documents/sqltabsdev/sqltabs/node_modules/sqldoc/build/SqlDoc.js:236:28
    at Array.map (native)
    at Constructor.render (/Users/jac/Documents/sqltabsdev/sqltabs/node_modules/sqldoc/build/SqlDoc.js:233:68)
    at ReactCompositeComponentWrapper._renderValidatedComponentWithoutOwnerOrContext (/Users/jac/Documents/sqltabsdev/sqltabs/node_modules/react/lib/ReactCompositeComponent.js:587:34)
    at ReactCompositeComponentWrapper._renderValidatedComponent (/Users/jac/Documents/sqltabsdev/sqltabs/node_modules/react/lib/ReactCompositeComponent.js:607:32)
    at ReactCompositeComponentWrapper.wrapper [as _renderValidatedComponent] (/Users/jac/Documents/sqltabsdev/sqltabs/node_modules/react/lib/ReactPerf.js:66:21)
    at ReactCompositeComponentWrapper.mountComponent (/Users/jac/Documents/sqltabsdev/sqltabs/node_modules/react/lib/ReactCompositeComponent.js:220:30)
    at ReactCompositeComponentWrapper.wrapper [as mountComponent] (/Users/jac/Documents/sqltabsdev/sqltabs/node_modules/react/lib/ReactPerf.js:66:21)
    at Object.mountComponent (/Users/jac/Documents/sqltabsdev/sqltabs/node_modules/react/lib/ReactReconciler.js:37:35)

These are the steps I followed, Terminal 1:

cd working_directory
git clone https://github.com/sasha-alias/sqldoc.git
cd sqldoc
npm install
npm run build:watch

Terminal 2:

cd working_directory
git clone https://github.com/sasha-alias/sqltabs.git
cd sqltabs
npm install
npm run build:watch

Terminal 3:

cd working_directory/sqltabs
install-local ../sqldoc/ ;  npm start

[sasha-alias]

I've never seen it, but try the above fix

[jackrsteiner]

I've made a pull request in sqldoc. There was one other example of a variable not scoped with var, so I just added the keyword. Now following the above steps works.