Latest Contributions by JoshuaBixby

SQL Server Express is good for what it is, a free and limited DBMS, but I don't think it will take much to start hitting resource limits in SQL Server Express once you take the 3-connection limitation away.  Depending on your usage, you might want to consider upgrading to SQL Server Standard or Enterprise or even PostgreSQL if cost is an issue for upgrading SQL Server. ... View more

Thinking back to the statement about a point-in-polygon query taking 17 hours to run, my guess is that you have some complex or dense municipal boundaries.  SQL Server, even Enterprise Edition, doesn't seem to handle point-in-polygon queries well when there are complex or dense polygons.  Some say it is tied to MS's implementation of quad-tree spatial indexes or a limitation of quad-tree indexes for spatial data.  I am not deep enough into databases to really have an informed opinion, but I do have to tweak spatial indexes much more in SQL Server than in PostgreSQL/PostGIS. For the complex/dense point-in-polygon situation, I think there is something more with how SQL Server implements some of its spatial methods.  If you either don't or can't go down the geoprocessing route, I encourage you to read through this MSDN forum thread on SQL 2K8R2: Any performance hints for bulk loading data from spatial/GIS db into a data warehouse?  Although the title doesn't make it seem relevant to this post, it is.  The author of the thread has an approach and some code for using tessellation or on-the-fly partitioning to break up complex polygons and dramatically reduce run times.  I have successfully used the technique and seen reductions of 90%+. ... View more

This one was on Esri.  The beta licenses expired, but the pre-release licenses were never automagically loaded when they were supposed to, which wasn't a problem until the beta licenses did expire.  New licenses were loaded, and we are back in business. Take home message, don't hesitate to contact your Esri Customer Service Representative if what you see in AGOL for Pro licenses doesn't make sense or seems like it should work but doesn't. ... View more

I agree with James Crandall‌ about SELECT * . Are you using pyodbc?  If so, fetchall() returns an empty list, even if no rows are returned, so I don't get why rows wouldn't be defined.  If a successful query, even one with no results, returns a list that could instantiate rows; maybe the queries are generating an error and fetchall is returning nothing, so rows doesn't exist. ... View more

We are on a large BPA/ELA which hasn't expired, or at least our current extension hasn't.  I have reached out to our Esri Customer Service Representative to help sort out what is going on.  A couple of our AGOL administrators said they never received an e-mail, so we have some work to sort out those mailing lists too. Once the dust settles, I will give an update in case others run into similar situations in the future. ... View more

Unfortunately, this is one of my gripes with the AGOL-based licensing scheme, i.e., AGOL administrators in larger organizations aren't necessarily in the same part of the organization as the GIS users because of the way ELA/contracts are established.  Maybe our admins got an e-mail, but would they even know what it meant if they actually read it. Appreciate the feedback, at least I now know who I have to go track down. ... View more

What, specifically, is the id of the record you are trying to select?  Is it 14036678?  If so, it is the underscore that is tripping you up.  The LIKE operator is for pattern/wildcard searching.  There are two operators, the percent sign (%) and the underscore (_).  The % operator matches zero or more characters while the _ operator only matches one character; thus, '11%' will match '11' while '11_' will not. ... View more

I think insert and update cursors were more the target for enabling with statements than search cursors, but people have a tendency to want to keep best practices simple.  Instead of having one best practice for insert and update cursors and a different one for search cursors, we have ended up with one for all three. I like list comprehensions, and I think they can work well with search cursors.  I was just pointing out the trade-offs of the two approaches, not trying to evangelize one over the other. ... View more

The documentation does give the impression that database locks will be released, but the documentation doesn't get into specifics of which locks.  Currently, using a with statement for da-based search cursor releases the read lock on the layer but not the schema lock.  My guess is that the schema lock can't be removed because the with statement resets the cursor iteration to streamline using the cursor again.  Explicitly deleting the cursor object will remove the final schema lock.  Is this poor documentation or a bug?  I haven't submitted a Support case to get an answer, but I am leaning toward Esri saying the former. ... View more

I agree a list comprehension can shave several lines off the code block, but we also lose the with statement.  I don't know whether it is formally documented, but I have read and heard several Esri staffers say using with statements is a best practice when working with cursors. ... View more

It is easier to look at code and provide feedback than statements describing the code.  What code are you using to build the table views? ... View more

Can you include more of the error message? ... View more

I did a quick performance check of the code above using pre-computed centroids stored and indexed versus generating centroids on the fly.  The latter approach took over 5x longer to run, and that was on a fairly modest-sized dataset. ... View more