Can't select features using SQL Server data connection

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Can't select features using SQL Server data connection

Elad
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: Can't select features using SQL Server data connection

Martin Morrison
Do you have a primary key set?

Martin Morrison     
Infrastructure Application Engineer/Systems Analyst
 Engineering Design Systems, Inc.
540.345.1410
[hidden email]


-----Original Message-----
From: fdo-users [mailto:[hidden email]] On Behalf Of Elad
Sent: Tuesday, June 26, 2018 3:16 AM
To: [hidden email]
Subject: [fdo-users] Can't select features using SQL Server data connection

Hi. I'm new to MapGuide and Maestro but I am expirienced programmer.
I have shape files and I have also imported them into MS SQL Server using shape2sql.
When I create a data connection directly to the shape files, and then create a layer for some feature, I am able to select features using the select tool on the map.
But, when I create the same layer using the SQL Server spatial feature source, the features are shown but are not selectable. When trying to select, it looks like it is processing (the red rectangle hangs for a while on the screen) but after few seconds, it says "0 features selected" and the features are not highlighted as using the shape file directly.

What I've tried so far: I susspected that when using the shape files directly, Maestro recognized the other files such as the .prj files with the coordinate system so in the data connection, I have set the coordinate system manualy to what ever it is in the prj file. It didn't help.

I have other issues such as slowness compared to working with the shape files directly.
I realize the shape2sql is outdated and perhaps misimported the data into the database, but it does show correctly on the map with all the other feature data.

Interstingly, when I set the tooltip to "TYPE" for example which is one of the feature data fields, it is shown! I can't select features even using the single feature selection.


Please help.
by the way, is there a forum for newbies?

Thanks.








--
Sent from: http://osgeo-org.1560.x6.nabble.com/FDO-Users-f3898664.html
_______________________________________________
fdo-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/fdo-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't select features using SQL Server data connection

Elad
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: Can't select features using SQL Server data connection

Martin Morrison

It SHOULD be significantly faster.  We don’t have enough information to figure out why.  You will need to check SQL indexes, server performance, network performance, etc…

 

Martin Morrison     
Infrastructure Application Engineer/Systems Analyst
 Engineering Design Systems, Inc.
540.345.1410
[hidden email]

cid:image001.jpg@01D2249F.40681A50

 

From: fdo-users [mailto:[hidden email]] On Behalf Of Elad Lavi
Sent: Tuesday, June 26, 2018 11:48 AM
To: [hidden email]
Subject: Re: [fdo-users] Can't select features using SQL Server data connection

 

Thanks! that was it. I appreciate it.

 

Can someone advise me about a reason why when using SQL Server as a data connection, it is significantly slower than when working directly with the shape files?

This is SQL Server 2014 Express. I imported the data from shape files to the database using shape2sql (which sadly didn't offer to add primary key).

Perhaps this is normal but I suspect that there's something I am missing with the FDO settings.

The database is on the same machine of course.

 

Thanks a lot.

 

 

 

 

 

On Tue, Jun 26, 2018 at 3:53 PM Martin Morrison <[hidden email]> wrote:

Do you have a primary key set?

Martin Morrison     
Infrastructure Application Engineer/Systems Analyst
 Engineering Design Systems, Inc.
540.345.1410
[hidden email]


-----Original Message-----
From: fdo-users [mailto:[hidden email]] On Behalf Of Elad
Sent: Tuesday, June 26, 2018 3:16 AM
To: [hidden email]
Subject: [fdo-users] Can't select features using SQL Server data connection

Hi. I'm new to MapGuide and Maestro but I am expirienced programmer.
I have shape files and I have also imported them into MS SQL Server using shape2sql.
When I create a data connection directly to the shape files, and then create a layer for some feature, I am able to select features using the select tool on the map.
But, when I create the same layer using the SQL Server spatial feature source, the features are shown but are not selectable. When trying to select, it looks like it is processing (the red rectangle hangs for a while on the screen) but after few seconds, it says "0 features selected" and the features are not highlighted as using the shape file directly.

What I've tried so far: I susspected that when using the shape files directly, Maestro recognized the other files such as the .prj files with the coordinate system so in the data connection, I have set the coordinate system manualy to what ever it is in the prj file. It didn't help.

I have other issues such as slowness compared to working with the shape files directly.
I realize the shape2sql is outdated and perhaps misimported the data into the database, but it does show correctly on the map with all the other feature data.

Interstingly, when I set the tooltip to "TYPE" for example which is one of the feature data fields, it is shown! I can't select features even using the single feature selection.


Please help.
by the way, is there a forum for newbies?

Thanks.








--
Sent from: http://osgeo-org.1560.x6.nabble.com/FDO-Users-f3898664.html
_______________________________________________
fdo-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/fdo-users


_______________________________________________
fdo-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/fdo-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't select features using SQL Server data connection

Hans Milling
In reply to this post by Elad
A few things to try:

1. Create a spatial index on your geometry column. This is very important or
SQL server has to go through all rows/geometry to find what is within the
current map window. This will make querying data very slow.
2. If you have any filters or rules, be sure to create index on the columns
used in these filters or rules. This can help a lot too with performance.
3. If you have thousands of points, line or polygons in a layer, change the
scale range (wich zoom level a layer is visible). Like an address layer
where you have house numbers. It makes no sens to display this when zoomed
all the way out. Change the scale range from infinity to like 1:1000 to make
the address first display when you zoom in. Optimising the scale ranges can
make your map perform a lot faster.

Best regards
  Hans Milling...



--
Sent from: http://osgeo-org.1560.x6.nabble.com/FDO-Users-f3898664.html
_______________________________________________
fdo-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/fdo-users
Reply | Threaded
Open this post in threaded view
|

Re: Can't select features using SQL Server data connection

Jackie Ng
To add to what Hans has said:

You *need* a primary key on your spatial table. Not only does this make your
spatial data selectable, it is also a pre-requisite for creating a spatial
index, which you also need.

Are you dealing with highly complex geometry data? Consider pre-computing a
Reduce()'d version of your geometry data and server that out at higher zoom
levels and serve the detailed version at lower zoom levels
(https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/reduce-geometry-data-type?view=sql-server-2017)

In terms of diagnosing slow performance, it really helps to understand
execution plans. This topic ventures into DBA territory so not knowing what
an execution plan is saying would be understandable.

- Jackie



--
Sent from: http://osgeo-org.1560.x6.nabble.com/FDO-Users-f3898664.html
_______________________________________________
fdo-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/fdo-users