Subscribe to this thread
Home - General / All posts - convert SQL manifold to radian
lionel113 post(s)
#13-Aug-17 23:02

1) Query builder with filter input is very nice to guess from manfiold source the equivalent function to use in radian .But what is the equivalent of manifold function to radian for the 3 bold SQL functions :

--SQL

CentroidX([Coord]) AS [x],

CentroidY([Coord]) AS [y]

FROM [Lines]

--SPLIT BY Coords([ID]) AS [Coord]

.

.

.

2) in querybuilder about input and output in function , querybuilder use <> to name structure ( <json> , <value> , <table>, <json>,<gml>,<string>,<valuex2|3|4> ) and sometime no structure ( <manifold>,<epsg><subtype><converter>,<component>,<tile>,<image>). Is there some documentation in the case <name> is about not structure/type name ?

I think only the documentation has the answer to know the structure in each specific SQL function name .

--<component> mean all object type locate inside windows panel name ( <drawing> , <image> ,<table> ).

--<tile> is colum type inside <table>

But for others <name> it is difficult to know where those name appearin the Radian GUI !

To be bale to test SQL function name using begining "? CALL " without ending by ";" like in "? CALL CoordDatums()" is nice .

A contextual menu to go directly in the function name to documentation ll be nice !! (see virtual capture screen )

I never find an so easy SQL wizard (in meantime i not an SQL developper so don't test a lot of SQL tools) and lack of some projection knowledge ( at this time ) ..... when use Manifold /Radian ...

regard's

Attachments:
manifold_querybuilderTOwebdoc.png

lionel113 post(s)
#14-Aug-17 21:12

centroid

v8 doc ; Centroid(geom): Returns centroid of given geometric object.

RS v9 doc : no centroid word in the page

coords

v8 doc Coords(geom): Splits geometric object into coordinates. A split function.

RS v9 doc GeomToCoords() :( even doc below don't use centroid word the value are the same )

Converts objects into their coordinates. Takes a geom and returns a table where each record contains a row for each coordinate of the object in the geom, with fields as follows:

  • Branch - index of current branch.
  • Coord - index of current coordinate from the beginning of geom.
  • XYZ - X, Y, Z coordinates as a float64x3 vector (NULL if there is no Z).
  • CoordInBranch - index of current coordinate within current branch.
  • XY - X, Y coordinates as a float64x2 vector.
Example: SELECT * FROM ( SELECT SPLIT CALL GeomToCoords([Geom]) FROM [Table]);

Base only on the the same mapinfo file import in M8 and RS9 i can locate value return by SQL in manifold and the new SQL in RS9 ( see capture screen).

To have the same result SQL in RS9 that the SQL in M8 ...my question is now using SQL in RS9:

-- How can we count Branch / coord from index of Branch / coord

-- How to Re SPLIT [X,Y] to X and Y locate in TABLE return by GeomToCoords

...still continue to search in doc ...

Attachments:
manifold_mif_file.png

lionel113 post(s)
#14-Aug-17 21:30

here a mapinfo of the line and v8 map project that contain a SQL query v8 and queryV9 .

import the V8map project inside RS9 should convert the v8 map content to V9 map content .

1+2) need to understand/master the Object model and GUI of manifold/Radian ....

3)why this SQL don't work in RS9 ?

--SQL9

SELECT  GeomToCoords([Geom])    FROM [Line]);

Attachments:
Line_query_M8toRS9_v8.map
online_mapinfo.zip

danb


1,603 post(s)
#14-Aug-17 22:25

The 9 SQL doesn't run because if you import the MFD8 project into Radian, the geometry column of the line is called [Geom (I)]. Change the query from [Geom] to [Geom (I)] and the query will run.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

adamw


7,227 post(s)
#15-Aug-17 06:38

...and there is no need to use two SELECTs. It works, but it is unnecessary, the query could be just:

--SQL9

SELECT SPLIT CALL GeomToCoords([Geom (I)]FROM [Line];

I will check why the example in the documentation uses two SELECTs.

lionel113 post(s)
#15-Aug-17 16:21

--SQL9

SELECT  GeomToCoords([Geom])               FROM [Line];--BAD

SELECT  GeomToCoords([Geom (I)])           FROM [Line];--BAD

SELECT * FROM ( SELECT SPLIT CALL GeomToCoords([Geom]FROM [Line]); --OK

SELECT SPLIT CALL GeomToCoords([Geom (I)]FROM [Line];--BAD

SELECT SPLIT CALL GeomToCoords([Geom])     FROM [Line];--OK

      --OK return table 

      --[Geom] is the column Name 

      --so not [Geom (I)] see Query Builder

Attachments:
forum_geomToCoords_test.png
geomToCoords_SQLtest_v9.map

adamw


7,227 post(s)
#15-Aug-17 06:44

-- How can we count Branch / coord from index of Branch / coord

-- How to Re SPLIT [X,Y] to X and Y locate in TABLE return by GeomToCoords

The table returned by GeomToCoords contains, for each coordinate: the branch number, the coordinate number from the start of the geom, the coordinate number from the start of the branch. Just use these fields.

To extract X or Y from an XY value represented as FLOAT64X2, use VectorValue. For example, this:

--SQL9

SELECT mfd_id, VectorValue(xy, 0) AS x, VectorValue(xy, 1) AS y

FROM (

  SELECT mfd_id, SPLIT CALL GeomToCoords([Geom (I)]FROM [Line]

);

...splits lines into a list of coordinates with X and Y fields.

lionel113 post(s)
#15-Aug-17 10:25

wondefull exemple .

I guess/feel we need to use SPLIT but your example show

- that the argument after SELECT is in a way an sub initialisation of variable ( allocation memory)

-and in the same time give a name to the allocated space ( AS x) ( non anonymous variable )

-then the FROM is the affectation part of the query that link memory to value .

perhaps what i writing is my own View ( MVC) of how really work this SQL statment . Need to code more to master this !!

My issue was that i don't know we can use VectorValue(xy,0) since xy=[x,y] with index starting using 0

-- SQL

(VectorValue(xy, 0) AS x, VectorValue(xy, 1) AS y ) = 

SPLIT CALL GeomToCoords([Geom (I)])

tjhb

7,503 post(s)
#15-Aug-17 14:05

Time to read a book.

lionel113 post(s)
#15-Aug-17 15:57

Happy to read that you recommand also this books . I re read also web page about SQL logic / group recently..

http://www.georeference.org/forum/t137371.13#137373

......first post that cover the availability of book : Visual Quickstart Guide , SQL, Chris Fehily

https://www.youtube.com/watch?v=8ROaz0YyzVg&feature=youtu.be

......video that use Radian viewer and books.map

http://manifold.net/updates/downloads.shtml

......the page where books.map can be download

......books.map (6592 KB)- A Radian format .map file providing the Books example database used in Chris

......Fehily's first-rate book, "SQL: Visual QuickStart Guide," a great book for learning SQL.

https://isbnsearch.org/isbn/<number_isbn> even virtual book sometime no isbn ? !!!

.............14, 2014 Copyright @2015 by Questing Vole Press 12$....ISBN 13/8: . . .-6-. . .-. . . . .-. 6 12$

.............Copyright @2008 by peachpit.com..........40$....ISBN 13/8: 978-0-321-55357-7 40$

.

.

Since many years i wanted to buy the art Lambo big book "An Introduction to Statistical Problem Solving in Geography" to see how GIS and stastistic questions are resolves . I mean use human language then use mathematic concept/tool then implement in a programming language ( SQL or not).

.

.

NB Art Lambo and Cie book is still only paper not virtual free DRM book and I always try to buy downladable book and try to avoid paper ( take place ) and Webservice reader ( after bad experience) .

.

NB I really like the history of monte carlo algo ....and some other like fourrier and see in which so many domains there are use now !!! since current appear and transistor replace tube.

Regard's

adamw


7,227 post(s)
#15-Aug-17 06:45

A contextual menu to go directly in the function name to documentation ll be nice !! (see virtual capture screen )

Yes, this is a good idea. We will add this once we have the API doc for SQL.

Geomatic1 post(s)
#15-Nov-17 00:39

In Manifold 8 I use the following query to generate lat/lon WKT from any projection:

SELECT [Field]

CStr(CGeomWKB(Project(Geom(ID), COORDSYS("Latitude / Longitude")))) AS [POLYGON]

FROM [Drawing]

In Radian Studio / Manifold Future I try to do the same but I don't know how to re-project within the query:

SELECT [Field], GeomWkt([Geom (I)]as [POLYGON]

FROM [Drawing];

Please help!

Thanks in advance.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.