{"id":2698,"date":"2018-05-17T09:17:10","date_gmt":"2018-05-17T07:17:10","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=2698"},"modified":"2018-06-07T08:31:32","modified_gmt":"2018-06-07T06:31:32","slug":"sparx-systems-enterprise-architect-sql-to-get-the-start-end-coordinates-of-the-link-routing-with-customized-lines","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=2698","title":{"rendered":"Sparx Systems Enterprise Architect: SQL to get the start \/ end coordinates of the link connector routing with customized lines"},"content":{"rendered":"

Problem<\/h1>\n

A SQL-Statement is needed (here Access \/ .eap) that gets all Coordinate-, Routing- and Position information of links in a diagram of the Sparx Systems Enterprise Architect.<\/p>\n

Approach<\/h1>\n

Data Mining and the Usage of MDB Plus<\/a><\/p>\n

Solution<\/h1>\n

The link routing is diagram dependent. In the following diagram the diagram „PLM Framework“ has been used. Further more the stereotype „BusinessProcess“ is requested (you have to replace these String according to your needs.<\/p>\n

\r\nSELECT bp.[Object_ID] as [start_object_id],\r\n       bp.[Name] as [start_object_name],\r\n       tconn.[Connector_ID] as [conn_id],\r\n       tconn.[Start_Edge] as [conn_start_edge],\r\n       tconn.[End_Edge] as [conn_end_edge],\r\n       tconn.[PtStartX] as [conn_pt_start_x],\r\n       tconn.[PtStartY] as [conn_pt_start_y],\r\n       tconn.[PtEndX] as [conn_pt_end_x],\r\n       tconn.[PtEndY] as [conn_pt_end_y],\r\n       tconn.[RouteStyle] as [conn_route_style],\r\n       linkpath.[Path] as [conn_path],\r\n       bpdest.[Object_ID] as [end_object_id],\r\n       bpdest.[Name] as [end_object_name],\r\n       bp.[Alias] as [start_key],\r\n       bpdest.[Alias] as [end_key],\r\n       startobjcoord.x as [start_obj_x],\r\n       startobjcoord.y as [start_obj_y],\r\n       endobjcoord.x as [end_obj_x],\r\n       endobjcoord.y as [end_obj_y],\r\n       linkpath.Geometry as [geometry] \r\nFROM\r\n((((( \r\n   SELECT Object_ID, [Alias], Name \r\n   FROM t_object\r\n   WHERE Stereotype='BusinessProcess'\r\n)bp\r\nINNER JOIN \r\n(  SELECT [Connector_ID],[Start_Object_ID], [End_Object_ID], [Start_Edge], [End_Edge], [PtStartX], [PtStartY], [PtEndX], [PtEndY], [RouteStyle] \r\n   FROM [t_connector] \r\n)tconn\r\nON tconn.[Start_Object_ID]=bp.[Object_ID])\r\nINNER JOIN \r\n( \r\n   SELECT Object_ID, [Alias],Name \r\n   FROM  [t_object] \r\n)bpdest ON tconn.[End_Object_ID]=bpdest.Object_ID)\r\nINNER JOIN\r\n(\r\n  SELECT ConnectorID, Path, Geometry\r\n  FROM t_diagramlinks WHERE DiagramID IN (SELECT Diagram_ID FROM t_diagram WHERE Name='PLM Framework')\r\n) linkpath ON linkpath.[ConnectorID]=tconn.[Connector_ID])\r\nINNER JOIN\r\n(\r\n  SELECT objstart.Object_ID,\r\n         objstart.Alias,\r\n         diaobj.RectLeft As x,\r\n         diaobj.RectTop As y\r\n  FROM\r\n  ((\r\n      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])\r\n                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])\r\n  WHERE dia.Name='PLM Framework'\r\n  AND objstart.stereotype='BusinessProcess'\r\n) startobjcoord ON startobjcoord.[Object_ID]=bp.[Object_ID])\r\nINNER JOIN\r\n(\r\n  SELECT objstart.Object_ID,\r\n         objstart.Alias,\r\n         diaobj.RectLeft As x,\r\n         diaobj.RectTop As y\r\n  FROM\r\n  ((\r\n      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])\r\n                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])\r\n  WHERE dia.Name='PLM Framework'\r\n  AND objstart.stereotype='BusinessProcess'\r\n) endobjcoord ON endobjcoord.[Object_ID]=bpdest.[Object_ID]\r\n<\/pre>\n