Subscribe to this thread
Home - General / All posts - convert SQL manifold to radian
lionel

457 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


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

457 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


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

457 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


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

danb


1,649 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

8,069 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.

lionel

457 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


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

adamw

8,069 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.

lionel

457 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)])


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

tjhb

8,227 post(s)
online
#15-Aug-17 14:05

Time to read a book.

lionel

457 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


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

457 post(s)
#23-Dec-17 12:33

here a good writing about anatomy of SQL ... from plamen ratchev/ .I copy paste the text here to avoid delete page

To write a good and correct query it is very important to understand the logical processing of a query. It is sad that very few SQL programming manuals start with that. Here is a look at the insides of logical query processing.

First, to start with a note: the logical processing of a query does not guarantee that the query will be executed that way. Since the cost based optimization of SQL Server goes through a few steps to find the optimal execution plan, it may choose to process the query different way that the sequence of steps below. Examples are when a WHERE predicate is applied in a JOIN, or when it is pushed down to the HAVING clause.

The logical query processing determines logically what the final result set would look like, regardless of how the database engine will manage to generate that result physically.

Here are the phases of the logical query processing in order of processing:

FROM: This is where it all starts! The FROM defines the source table(s) for the processing. If more than one table is included, then a Cartesian product (cross join) is performed between the first two tables. The result of the Cartesian product is every possible combination of a row from the first table and a row from the second table. That is, if one of the tables has M rows and the other one has N rows then the result set will be M x N rows.

ON: Next, the ON filter is applied to the result set. Only rows that match the join condition are selected.

OUTER: If an OUTER join is used, then rows from the preserved table for which a match was not found are added back. In INNER joins this phase is skipped. If more than two tables are in the FROM clause, then FROM/ON/OUTER are applied between the current result set and the next table, until all tables are processed.

WHERE: The next step is to apply the WHERE filter to the result set. Rows that satisfy the where conditions are selected. Only conditions that evaluate to TRUE (not UNKNOWN or FALSE) will pass.

GROUP BY: The result set rows are divided in groups based on the column list specified in GROUP BY. Rows of the grouped set must be only grouping columns, aggregate functions (i.e. COUNT, SUM, AVG, MIN, MAX), function or constants, and an expression made up of the first three items. Important to note here is that NULL values are considered as equal and grouped into one group.

HAVING: Next, the HAVING filter is applied. Only groups that match the having conditions are selected. If a group doesn’t satisfy the conditions, then the whole group is removed from the result set. If there is no GROUP BY the entire result set is treated as one group.

SELECT: Here it is, the SELECT list! The SELECT list is processed down here, even that it is the first line in the query. At this time column aliases are added. Steps prior to this in the list cannot use the column aliases, only the following steps will see the aliases. This is the step that will define the columns in the result set.

If there is a SELECT DISTINCT, then the duplicate rows are removed. Just like in GROUP BY, NULLs are treated as matching.

ORDER BY: The rows of the result set are sorted according to the column list specified in the ORDER BY clause. Only using ORDER BY can guarantee a sort order for rows. Otherwise the tables are unordered sets. At this step the result set is transformed to a cursor. Also, NULLs are considered equal for sorting. The ANSI standard doesn’t allow to order by columns that are not included in the SELECT list, but SQL Server allows doing that (even by expressions based on those columns). Columns in the ORDER by can be referred to by the alias or by their ordinal number.

A couple more notes. The CUBE & ROLLUP are processed after GROUP BY. At that point the super-groups are added to the result set. Also, TOP which is T-SQL specific is processed after ORDER BY, and there the specified number or percentage or rows are selected in the final result set.

For nested queries, the innermost queries can reference columns and tables in the queries in which they are contained.

Greate that art LAmbo has video refer to his book but silly behaviour that price change refer to parameters on url

30 $ => go to

https://www.udemy.com/statistical-problem-solving-in-geography/?couponCode=gisadvisor.com&siteID=TnL5HPStwNw-rbT2n136g_JWkyDT9KXM3A&LSNPUBID=TnL5HPStwNw

15$ = > go to

https://www.udemy.com/statistical-problem-solving-in-geography/

regard's


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

adamw

8,069 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.