Latest Contributions by MarcoBoeringa

I saw that error message about the Spatial Statistics Tools toolbox popping by as well at one point when opening Pro. However, after another restart of Pro, this seems resolved and I can see the toolbox: A more disconcerting issue for me though, is that I cannot seem to get the Conda managed Python environment to switch. At 2.2, the default Pro environment has become read-only according to the Help, and you are forced to create a clone or new environment to make changes and install packages. However, I for the life of me, cannot get this to work: - As the Help stated, I cannot change or install a package to the default environment in 2.2 - Creating a Clone seems initially to work, but once I select the clone and close the dialog, I am left with an empty enviroment dialog: - When I restart Pro, as the dialog warned me to do, the "test" environment I created and that IS visible in my user profile as a new folder, is not recognized by Pro: So I am currently stuck with the default environment, that I cannot change because it is now read-only at 2.2... ... View more

Could you post screenshots here of how you view some of this data in ArcMap, ArcGIS Pro and Adobe Acrobat? Just hit the "Print Screen" button on your keyboad and paste in a graphics application if you want to crop the image, or straight in a GeoNet editor window, which also works. One fundamental difference though between ArcMap and Pro, that also may affect how you "view" the PDFs, is that Pro currently maintains all coordinate information of the original data in the PDFs, while ArcMap does a kind of "generalization" to the dpi specified during export. This difference, as I understood it, has to do with some fundamental, Windows level, differences in the display pipeline of ArcMap and Pro. As to some observation by me regarding this, see this post I created in another GeoNet thread: https://community.esri.com/thread/179579#comment-710329  As a consequence, if you have highly detailed data derived at large scales (e.g. 1:5k-1:25k), and view it a small scale (e.g. 1:100k-1:M) without generalizing the data yourself using a tool like "Simplify Line/Polygon", the data may appear very erratic and with "spikes" in a Pro export if the data is also following erratic paths, while ArcMap's output may look smoother and closer to what you would have expected. Especially look closely at the screenshots I posted there to understand what I mean and the differences this may cause in the visible image on your screen. ... View more

Are you using Adobe Reader to view your PDFs? If so, be aware that Adobe Reader has a number of "enhancement" options for viewing vector based PDFs. One of them is called "Enhance thin lines", which you can find in the Edit / Preferences / Page view / Rendering menu option. This option does exactly what you are witnessing: make thin lines (e.g. 0.2-0.4pt) wider for display. This is a really bad option / setting of Adobe Reader in the context of cartographic maps. Adobe made the unfortunate decision to set this option to on by default. If you review past posts in discussion forums of graphic designers, there is quite a list of designers lamenting this descision to make this option default on, as it wrangles their designs as well. So, switch the Enhance thin lines option off in Adobe Reader! Note that I have even witnessed this option affecting the display of vector true/open type fonts, and causing artefacts in the rendering and anti-aliasing on especially fonts set to Bold. This is another good reason to switch this option off. ... View more

How are you actually adding the data to Pro? I recently opened another discussion thread regarding similar issues accessing data stored in a non-geodatabase spatial database in PostgreSQL through Query Layers in Pro. https://community.esri.com/thread/216224-fundamental-flaw-when-opening-attribute-tables-in-arcgis-pro Like you, I was working on really large datasets (In my case even >65 up to 165M records), using Query Layers to access the data, and I am pretty much convinced ESRI never tested this functionality at scale using similar sized datasets given the issues I experienced. I also saw bad performance accessing such data. One thing that has become clear, is that ArcGIS attempts to calculate the spatial extent of any spatial table you access, causing full table scans on the data for an apparently trivial cause / reason. This is one crucial difference with geodatabases, where the maximum spatial extent is probably registered in the geodatabase system tables, and thus only needs to be determined once. I am deeply convinced though, that ESRI should review the entire access of non-geodatabase spatial databases, and allow it to scale. E.g., if the spatial extent calculation is so important for ArcGIS, but can't scale with the current implementation, why not take a random sample of e.g. 1% of all records to get an estimate of the extent? Since ArcMap allows you to override the spatial extent calculation once you add a Query Layer to ArcMap using either a user provided exent, or the spatial reference maximum valid extent, the exact size of the spatial extent doesn't seem to be relevant, and an estimate probably is enough. One big tip though when accesing such data: Make sure you always include the entire selection clause in the WHERE clause of the Query Layer. Do not add part of the SQL in the Definition Query property of the layer. This is much more efficient than having part of the WHERE clause as Definition Query. E.g. "SELECT * FROM <TABLE_NAME>" as Query Layer + "<FIELD> = X" as Definition Query is NOT the same as / equivalent to: "SELECT * FROM <TABLE_NAME> WHERE <FIELD> = X" as Query Layer from a performance point of view in the current ArcGIS implementation of accessing non-geodatabase spatial tables. The latter is much faster if the WHERE condition is selective, as the former is in essence considered one giant table without any selection. ... View more

Don't know if it is of any use to you, but I recently explored the options to create spatial views using pyodbc and the PostgreSQL ODBC drivers. This seemed to work quite nicely, and I could bring the data back into ArcGIS Pro using Query Layers. You do need to mind data columns you add to the view. Some column types are not supported by ArcGIS. E.g. PostgreSQL's hstore column type for key-value storage cannot be part of the final result of columns in the view (you can use it deeper down in the SQL, just not in the final columns exposed by the view). pyodbc is part of the default Python install of ArcGIS Pro. I only needed to install the PostgreSQL ODBC drivers. pyodbc also supports other databases besides PostgreSQL: Home · mkleehammer/pyodbc Wiki · GitHub  ... View more

Hi Michael, I have explored all three possibilities: 1) - Inserting complex SQL statements straight into the Query Layer's SQL (which is of course, and slightly understandably, the least optimal option from a performance perspective). 2) - Created "logical" or normal spatial views at the database level using the ESRI "Create Database View" geoprocessing tool (although this could just as well be done using PostgreSQL compatible tools like the DBeaver I have been using) and inserting the same SQL of the first option as view definition. 3) - Materializing the views created in 2) and accessing these from ArcGIS using a Query Layer (note again this is non-geodabase spatial database). Of course, this option is by far the most performant, especially since you can index the materialized views as well, which I did. Nonetheless, all three options ultimately require a Query Layer to access the data, the Query Layer's SQL statement just simplifies with options 2) and 3). Although I have written in my last post that part of the issues I see, may not be a bug but "by design", I do still think the chosen solutions for handling Query Layers are suboptimal concerning the spatial extent calculation and the usage of a potential Definition Query on the layer to limit the number of records: A "Definition Query" is exactly what its name describes: it is supposed to "define" what records belong to a layer. Ignoring the Definition Query in the context of a Query Layer as seems to happen in the specific cases I encountered, is at the very least sub optimal. Anyway, as I also wrote before, there may be issues or limitations ESRI has to deal with that I cannot really understand right now. Let's see what ESRI has to tell when I get a response on my support call. I have at least had confirmation of the Dutch branch of ESRI that they put this call through to ESRI inc, so let's wait for the response. ... View more

For comparison, I have now also looked at what happens in ArcMap, and there does seem to be some vital differences. What I did: - Drag & drop the huge table in ArcMap (You can't actually do this yet in Pro by the way!) from ArcMap's Catalog window. ArcMap then opens a dialog about the need to calculate the "spatial extent". This is also a slow operation in ArcMap, which also seems linked to a full table scan (which I also do not fully understand, can't this information be derived from the spatial index much faster?). The SQL of the resulting Query Layer is equivalent to the SELECT * FROM TABLE_NAME of the last post, albeit with the * wildcard replaced by a full list of all the database columns of the source table. - Without any Definition Query set, I opened up the Attribute Table in ArcMap. This is still a slightly costly operations, taking one or a few minutes, but not nearly as bad as in Pro taking half an hour in the same situation. Now, when I look at the PostgreSQL logs, I see at least one difference with Pro. Opening the attribute table seems to happen in two steps. Notice that when the first BINARY CURSOR is created, the SQL statement contains a WHERE clause with what appears to be a spatial constraint. I think I saw this step happening in Pro as well. Anyway, when you look at the second highlighted BINARY CURSOR being created, which represent the moment ArcMap really starts creating the actual contents for the Attribute Table of the Query Layer, you will notice (highlighted in blue) that ArcMap actually does restrict the record set to the first 100 records or so, by inserting their OBJECTIDs as part of the a large IN (...) statement. I also did another observation regarding ArcGIS Pro: when I open the Attribute Table the first time, it takes half an hour. Any subsequent opening of the Attribute Table is much faster, and seems equivalent to ArcMap. As a consequence, I am now getting convinced that the behavior I am seeing is actually NOT A BUG, but a change in how ESRI decided to handle Query Layers and specifically  the calculation of the spatial extent of such Query Layer: - In ArcMap, when you drag a spatial database in the TOC, a dialog opens up explicitly warning you that the "spatial extent" (Calculate Extent is the title of the small dialog) is being calculated and that this may be a costly operation (likely due to the full table scan taking place in the background), and even offers override options by allowing you to manually enter them, or use the Spatial Reference's maximum extent. - In ArcGIS Pro, no such dialog pops up. Instead, it seems to leave a kind of "unitialized" layer in the TOC. What then happens is that Pro appears to start calculating this spatial extent the first time you open up the Attribute Table or use some geoprocessing tool on the layer, like I have witnessed with the "Apply Symbology From Layer" tool that takes forever to open up when such a Query Layer layer is present. Afterwards, this information seems cached, and being re-used the next time you e.g. open the Attribute Table of the Query Layer. While Pro's implementation takes away the "annoying" spatial extent calculation dialog that pops up in ArcMap, the ArcMap method, with an explicit calculation when you add the data, is far more desirable for large layers, as it won't lock up the application in an unexpected manner for long periods of time without the user knowing why. If I am right, I also think the calculation of the spatial extent of Query Layers really needs to be reviewed by ESRI and find out if there isn't a much more efficient way to do this, e.g. by taking a "random sample" of e.g. 1% of the records in case of large tables. It now seems to require a full table scan both in ArcMap and Pro, which is outrages in the context of database tables of tens of millions of records. I do not know if there are restrictions I don't understand, that ESRI has to abide by, that cause the current implementation in ArcMap and Pro regarding this, but it would be highly desirable to have a much more efficient method for this, thus taking away a performance bottleneck on Query Layers based on millions of records. ... View more

I have been doing a bit further investigation, and I think I now better understand what happens. To explain this, look at the following to scenario's: 1) A Query Layer with a SELECT * FROM TABLE_NAME type query, and a layer's Definition Query set to e.g. FIELD_NAME = 'X',  so no WHERE clause included in the SQL statement of the Query Layer, but only in the Definition Query. This situation always triggers a full table scan. It seems ArcGIS only evaluates the Query Layer's SQL statement when communicating with the database, and ignores the Definition Query. The Definition Query is only evaluated after or during the full table scan at what must be the ArcGIS Pro application level, instead of in the database. Opening an attribute table of a very large table of such a layer, is thus very slow due to the full table scan. If there is no Definition Query set and you attempt to open the Attribute Table, Pro also seems to do a full table scan. I think it shouldn't, it should only request the first 100 or 2000 or whatever number of records of the table, and present those to the user, e.g. using a LIMIT request to the database. Since there is no Definition Query set, it doesn't really matter what records of the (potentially huge) underlying table are shown to the user. Any records will do. This would be much faster. 2) A Query Layer with a SELECT * FROM TABLE_NAME WHERE FIELD_NAME = 'X' type query, so the WHERE statement included directly in the Query Layer's SQL, and a layer's Definition Query empty or some other query. This situation does cause Pro to send WHERE clauses to the database, albeit being based on OBJECTIDs. If the WHERE only selects a small amount of records from the total records set of the table it references, the Query Layer's attribute table will open fast. So the big take away from this is that, whenever possible and especially with huge database tables with tens of millions of records, you should always directly insert a subsetting WHERE statement in the Query Layer's SQL statement directly, and not in the Definition Query. You would in essence expect these two scenarios to be equivalent, but from the application point of view - at least with the current implementation! - a Query Layer with no WHERE in the SQL of the Query Layer itself, but just in the Definition Query, is the equivalent of just dragging and dropping the entire spatial database table in the TOC. ... View more

I think I have another interesting observation to share, that also reveals some of the "inner workings" of Query Layers, and their intertwinement with what ESRI used to call SDE but is actually the foundation of the access to databases in ArcGIS products. If you look at the screenshot, you see again two SQL statements, this time run against a much smaller PostgreSQL database than the original one I posted about, but a comparable situation with a Query Layer. The first highlighted SQL statement was issued to the database once I clicked the layer visible in the TOC, and shows actually two WHERE clauses: one I added to the Query Layer's SQL statement directly: WHERE osm_highway = 'secondary', and the other one I added as a Definition Query of the same layer: where ((osm_name = 'Rua Nova')). Further down highlighted in blue is the SQL statement that was fired when the I opened the attribute table. There are in fact four records that have satisfy the osm_name = 'Rua Nova' clause, however, interestingly, this clause is not included as the WHERE statement, but instead the actual OBJECTIDs of the records corresponding to the clause mentioned. It appears, once a layer is created, all the OBJECTIDs corresponding to the layer's defining SQL statements, are likely kept in memory and submitted to the database in one long IN (...) statement. That is the "inner workings" I meant. So Pro does submit a WHERE clause in this case, even though it is not the one I expected. Even more puzzling of course, is that this seems to work for this small layer, but failed on the giant 65M record layer. Now I am pretty sure, and there is of course no reason for this, that Pro won't attempt to build an IN (...) clause for 65M records all at once, which would probably fail, but instead likely only needs to build such IN (...) statement for the first 2000 records or so that an attribute table usually displays once it opens (you can't show 65M records on a screen at once...). But even this likely limited operation seems to fail in the case of this giant layer. ... View more

Michael Volz schreef: @Marco: ...   Would you consider the behavior in the below thread another fundamental flaw with a query layer going back to database for a full table scan:   Available Values for a Query Layer Subset Selection  Can't tell from your description. You really need to be a bit more specific in the problem description. How did you "perform a query on the data to get a subset" or "create a selection from the subset of values"? What tools or menu options did you exactly use? As to being presented with a list of unique values not related to the selection / query: - In my opinion, it would be better if there was at least an option to have the presented unique values restricted to the initial query, or the full list of unique values of a column. - Being presented with the full list of unique values of the entire column does not necessarily mean a full table scan is taking place. If there is a proper index on the column, the database likely returns the necessary result from there. Do you see any indications of a true full table scan, like (extremely) long delays on layers with many records, like I did? The reason why I called the issue I presented a "fundamental flaw" is that I really think any system like a GIS that works with huge datasets, should try to avoid doing full table scans at all cost. Full table scans on giant datasets are costly, and can cause a huge amount of unnecessary network traffic in a case like the one presented here. If there is any source for a SQL restriction, like a spatial constraint (being zoomed in), or any form of valid attribute selection (whether directly included in a Query Layer's source SQL statement or in the Definition Query of the layer as in my case), it should be applied. In fact, there is a discrepancy here as well in Pro, if you look at my Original post, you can see that ArcGIS Pro, does append the Definition Query to the SQL used to create the Query Layer when I open the saved Map document, it just doesn't do it when the attribute table is being opened. ... View more

@George Thompson, At your request I just send in a support question to the Dutch branch of ESRI, pointing them to this GeoNet thread. Don't know if they will respond though, but hopefully they will. I referenced your request in the e-mail by the way, I hope you don't mind. Marco ... View more

No, this is just PostgreSQL I am working with. I could attempt to replicate your ORDER BY issue though in PostgreSQL if you are interested to know such result, although it likely adds little value for an already logged bug. ... View more

@George Thompson: To clear up a few points: - Yes, this issue I detected relates to a non-geodatabase spatial database, and the layers added to the TOC are ArcGIS Query Layers. - This issue has little to do with indexes. The basic problem is no WHERE clause being submitted. You can have a fully indexed database, but if Pro, despite the Query Layer having a Definition Query set, does not submit a WHERE clause to PostgreSQL when opening the attribute table of a Query Layer, then indexes are of no use and the only result of the SQL statement being submitted to PostgreSQL can be a full table scan... Actually, I find it somewhat of a mystery that ArcGIS Pro, after the full table scan, does come up with the correct attribute table result (restricted to the WHERE clause that the Definition Query represents). Clearly Pro must be evaluating the records returned by the FETCH FORWARD 1000 statements on the fly, as otherwise it should return the entire table. - Yes, please do forward this to a support analyst on the Geodata team. ... View more

OK, this particular issue with the apparently missing osm_piste_58_abandoned field seems (partly) false alarm. I don't know if this is actually part of the SQL standards, but it seems PostgreSQL is fault tolerant in this respect, and silently adds the required field to satisfy the WHERE condition. I conclude this based on creating a similar statement in DBeaver, that does return results. Nonetheless, it would be nicer to actually see such field explicitly included in the SQL send to the database, also for consistency reasons, as the osm_piste_58_type and osm_piste_58_difficulty fields that are also part of the WHERE statement, are included as I already stated in the previous post: Only when I exclude the osm_piste_58_abandoned from the inner SELECT, does it fail: ... View more

I am now even more confused about what is going on. If you look closely at the first SQL statement that represents the generation of the TOC layer, it appears the required osm_piste_58_abandoned field, that is part of the Definition Query and thus the WHERE clause, is not listed in the field list of the BINARY CURSOR. The other required fields for evaluating the WHERE clause (osm_piste_58_type and osm_piste_58_difficulty) are listed: The SQL statement thus shouldn't even be valid, yet ArcGIS Pro opens up happily without error and the layer seems OK once I inspect the attributes?... ... View more